2

Need to convert this timestamp (001281379300724) to YYYY-MM-DD hh:mm:ss format in SQL Server, if possible. Any suggestions?

user1541301
  • 325
  • 1
  • 4
  • 14
  • 2
    It would help if we knew roughly where you got the "timestamp" from, how that "timestamp" was created, etc... – Adam Jul 20 '12 at 16:31
  • 3
    It's most likely seconds since the UNIX epoch. In the example you linked that would be Mon, 09 Aug 2010 18:41:40 GMT. – orlp Jul 20 '12 at 16:32
  • 2
    Is it the number of milliseconds since 1971? UNIX time – Ryan Gray Jul 20 '12 at 16:32
  • That looks like a unix timestamp. See [this answer](http://stackoverflow.com/questions/2904256/how-can-i-convert-bigint-unix-timestamp-to-datetime-in-sql-server) – Anirudh Ramanathan Jul 20 '12 at 16:32
  • This is probably the UNIX seconds counter starting from Midnight of January 1, 1970. You can calculate from there the current timestamp – Gunnar Jul 20 '12 at 16:33
  • and this answer http://stackoverflow.com/questions/4787827/converting-epoch-timestamp-to-sql-serverhuman-readable-format – 8bitwide Jul 20 '12 at 16:37
  • 1
    @nightcracker Mind the number of digits. I think RyanGray might be right. 1281379300724 milliseconds would be Aug 2010, but 1281379300724 whole seconds would be over 40,000 years from now. – Wiseguy Jul 20 '12 at 16:37
  • If this is not a value from a SQL timestamp column, do you at least have an example of known values and their corresponding dates in the format you want? – JamieSee Jul 20 '12 at 16:47

3 Answers3

3

This presumes the timestamp is ms since UNIX epoch. It only converts to the nearest second, but you could add ms to it(see below). It has to use two steps since dateadd requires an int. First add minutes by dividing by 60000, then add seconds. 

DECLARE @yournum bigint
SET @yournum = 1281379300724     
SELECT DATEADD(ss, (@yournum / 1000)%60 , (DATEADD(mi, @yournum/1000/60, '19700101')))

Gives 

 2010-08-09 18:41:40.000

To get ms precision: (yuck, probably a better way)

DECLARE @yournum bigint
SET @yournum = 1281379300724     
SELECT DATEADD(ms, (@yournum%1000),DATEADD(ss, (@yournum / 1000)%60 , (DATEADD(mi, @yournum/1000/60, '19700101'))))

Gives

2010-08-09 18:41:40.723
Ghost
  • 2,146
  • 19
  • 21
1

The simple answer is that if this is a SQL timestamp column (a.k.a rowversion), you can't. Per the documentation for the type:

Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database. This counter is the database rowversion. This tracks a relative time within a database, not an actual time that can be associated with a clock.

...

The Transact-SQL timestamp data type is different from the timestamp data type defined in the ISO standard.

JamieSee
  • 12,696
  • 2
  • 31
  • 47
1

You can get slightly closer this way:

SELECT DATEADD(MINUTE, 1281379300724/1000/60, '19700101')

Result:

2010-08-09 18:41:00.000
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490