How to convert SQL Server's timestamp column to datetime format

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

SQL Server's TIMESTAMP datatype has nothing to do with a date and time!

It's just a hexadecimal representation of a consecutive 8 byte integer - it's only good for making sure a row hasn't change since it's been read.

You can read off the hexadecimal integer or if you want a BIGINT. As an example:

SELECT CAST (0x0000000017E30D64 AS BIGINT)

The result is

400756068

In newer versions of SQL Server, it's being called RowVersion - since that's really what it is. See the MSDN docs on ROWVERSION:

Is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The rowversion data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime2 data type.

So you cannot convert a SQL Server TIMESTAMP to a date/time - it's just not a date/time.

But if you're saying timestamp but really you mean a DATETIME column - then you can use any of those valid date formats described in the CAST and CONVERT topic in the MSDN help. Those are defined and supported "out of the box" by SQL Server. Anything else is not supported, e.g. you have to do a lot of manual casting and concatenating (not recommended).

The format you're looking for looks a bit like the ODBC canonical (style = 121):

DECLARE @today DATETIME = SYSDATETIME()

SELECT CONVERT(VARCHAR(50), @today, 121)

gives:

2011-11-14 10:29:00.470

SQL Server 2012 will finally have a FORMAT function to do custom formatting......