CODEWITHSUNDEEP
sql-serverdatetimetimestamp
Jayson
Jayson

Reputation: 2193

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

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

Upvotes: 124

Views: 1106623

Answers (9)

Andrew Peterson
Andrew Peterson

Reputation: 19

Robert Mauro has the correct comment. For those who know the Sybase origins, datetime was really two separate integers, one for date, one for time, so timestamp aka rowversion could just be considered the raw value captured from the server. Much faster.

Upvotes: 1

Baidiuk Yevhen
Baidiuk Yevhen

Reputation: 79

for me works: TO_DATE('19700101', 'yyyymmdd') + (TIME / 24 / 60 / 60) (oracle DB)

Upvotes: 0

user1575120
user1575120

Reputation: 339

After impelemtation of conversion to integer CONVERT(BIGINT, [timestamp]) as Timestamp I've got the result like

446701117 446701118 446701119 446701120 446701121 446701122 446701123 446701124 446701125 446701126

Yes, this is not a date and time, It's serial numbers

Upvotes: 0

Mac
Mac

Reputation: 69

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. That's what this type is meant to be.

If you want a date - just add a Datetime column and live happily ever after :)

cheers mac

Upvotes: 6

marc_s
marc_s

Reputation: 755381

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

Upvotes: 306

Ali
Ali

Reputation: 343

Using cast you can get date from a timestamp field:

SELECT CAST(timestamp_field AS DATE) FROM tbl_name

Upvotes: 6

jrobiii
jrobiii

Reputation: 141

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

Upvotes: 3

Sudeep nayak
Sudeep nayak

Reputation: 436

The simplest way of doing this is: 

SELECT id,name,FROM_UNIXTIME(registration_date) FROM `tbl_registration`;

This gives the date column atleast in a readable format. Further if you want to change te format click here.

Upvotes: 9

Jadwiga
Jadwiga

Reputation: 65

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

Upvotes: 4

Related Questions