Clever Geek Handbook

MSSQL - Convert milliseconds since 1970 to datetime2

Consider the following query (in MSSQL 2008):

SELECT dateModified FROM SomeTable;

Returns a float in javascript format (milliseconds since 1970):

dateModified 
============ 
1301598290687 
1071003581343 
1311951478593

How can I convert this to datetime2 directly in select?

+3


source to share


2 answers


Using @Mikeal Eriksson's formula answer here .

I would convert the float to bigint and then create the date and time:



select 
  DATEADD(MILLISECOND, 
          cast(dateModified as bigint) % 1000, 
          DATEADD(SECOND, cast(dateModified as bigint) / 1000, '19700101'))
from sometable

See SQL Fiddle with Demo

+8


source


Oracle example - replace to_date () with eqivalent:

  SELECT (1301598290687/60/60/24/1000) as Days
   , to_date('01-01-1970','dd-mm-yyyy') as start_date
   , to_date('01-01-1970','dd-mm-yyyy')+(1301598290687/60/60/24/1000) as converted_date
  FROM dual
  /

DAYS                START_DATE      CONVERTED_DATE
---------------------------------------------------------
15064.7950310995    1/1/1970        3/31/2011 7:04:51 PM


Create a double table:

CREATE TABLE DUAL
(
DUMMY VARCHAR(1)
)
GO
INSERT INTO DUAL (DUMMY)
 VALUES ('X')
GO
-1


source






More articles:


All Articles