How to convert SQL Server's timestamp column t

Question

As SQL Server returns timestamp like 'Nov 14 2011 03:12:12:947PM', is there some easy way to convert string to date format like 'Y-m-d H:i:s'.

So far I use

date('Y-m-d H:i:s',strtotime('Nov 14 2011 03:12:12:947PM'))

Answer 1

Some of them actually does covert to a date-time from SQL Server 2008 onwards.

Try the following SQL query and you will see for yourself:

SELECT CAST (0x00009CEF00A25634 AS datetime)

The above will result in 2009-12-30 09:51:03:000 but I have encountered ones that actually don't map to a date-time.

Answer 2

Why not try FROM_UNIXTIME(unix_timestamp, format)?

Answer 3

"You keep using that word. I do not think it means what you think it means." — Inigo Montoya

The timestamp has absolutely no relationship to time as marc_s originally said.

declare @Test table (
     TestId int identity(1,1) primary key clustered
    ,Ts     timestamp
    ,CurrentDt datetime default getdate()
    ,Something varchar(max)
)

insert into @Test (Something)
    select name from sys.tables
waitfor delay '00:00:10'

insert into @Test (Something)
    select name from sys.tables

select * from @Test

Notice in the output that Ts (hex) increments by one for each record, but the actual time has a gap of 10 seconds. If it were related to time then there would be a gap in the timestamp to correspond with the difference in the time.

Answer 4

Not sure if I'm missing something here but can't you just convert the timestamp like this:

CONVERT(VARCHAR,CAST(ZEIT AS DATETIME), 110)

Answer 5

Works fine, except this message:

Implicit conversion from data type varchar to timestamp is not allowed. Use the CONVERT function to run this query

So yes, TIMESTAMP (RowVersion) is NOT a DATE :)

To be honest, i fiffffdled around quite some time myself to find a way to convert it to a date.

Best way is to convert it to INT and compare. Thats what this type is meant to be. If You want a date - just add a Datetime row and live happyly ever after :)

cheers mac

Answer 6

Using cast you can get date from a timestamp field:

SELECT CAST(timestamp_field AS DATE) FROM tbl_name