问题描述:

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'))

网友答案:

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

It's just a binary representation of a consecutive number - it's only good for making sure a row hasn't change since it's been read.

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......

网友答案:

Using cast you can get date from a timestamp field:

    SELECT CAST(timestamp_field AS DATE) FROM tbl_name
网友答案:

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 fidddled 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

网友答案:

My coworkers helped me with this:

select CONVERT(VARCHAR(10), <tms_column>, 112), count(*)
from table where <tms_column> > '2012-09-10'
group by CONVERT(VARCHAR(10), <tms_column>, 112);

or

select CONVERT(DATE, <tms_column>, 112), count(*)
from table where <tms_column> > '2012-09-10'
group by CONVERT(DATE, <tms_column>, 112);
网友答案:

Some of them actually does covert to a date-time from SQL Server 2009 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.

网友答案:

why not try FROM_UNIXTIME(unix_timestamp,format)

网友答案:

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)
相关阅读:
Top