Posted By: Anonymous
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'))
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
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
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)
SQL Server 2012 will finally have a
FORMAT function to do custom formatting……