Help! Need to extract date/time from SQL database table

Please help! How do I extract mm/dd/yyyy hh:nn from a timestamp field located in an MS SQL 2000 database table?

I can do this in MS Access where , for example only, I can convert 56323938 to "07/24/2006 11:47"

But when i attemp to extract the same info from the same field in SQL Query Analyzer, I only get "07/24/2006 00:00".

Can someone help me ? Is there a stored procedure or sql string that I can use that will in fact return the "mm/dd/yyyy hh:nn" from a timetamp field (where the hh:nn is actual and not fillers like "00:00"?

:)Dear,
The ODBC and OLE DB timestamp data type records dates and times. The Transact-SQL timestamp data type is a binary data type with no time-related values.
May You get some idea from this.otherwise there should be no problem in retrieving time through (select <date_column> from <table>),i suppose.
Regards,
Lipsa|||Try

select convert( datetime, <timestamp_column> ) from <table>

Rgds

mjg|||I now recall how i got the mm/dd/yyyy hh:mm am/pm from a timestamp

I used the following string:

select convert(datetime,cast( [timestamp] as decimal)/1440 + 1,101) from table

(timestamp is the sql encoded stamp integer field stored on a table. By converting the [timestamp] field to decimal instead of "datetime" allows the hh:mm:ss to be extracted where the "/ 1440 + 1" extracts the date from the timestamp.

:)

Quote:

Originally Posted by MikeG

Try

select convert( datetime, <timestamp_column> ) from <table>

Rgds

mjg