SQL server convert 13 char long unix date strings into datetime data type

Question

I have two dates as strings as below. They are in unix time format.

1364810610810+0530 and 1364830410810

I need to convert this to SQL server datetime type with SQL Server 2008 R2 and print the values on to console to see the milliseconds as well. Can someone help me with this.

I figured that 1364830410810' string can be converted as below. But it does not print milliseconds.

declare @myDate datetime;
set @myDate = DATEADD(SECOND, 1364830410810/1000 ,'1970/1/1');
print 'Second Statement: ' +  Convert(varchar(100), @myDate );

Have you made some search before asking? [UNIX timestamp to datetime in SQL Server](http://stackoverflow.com/a/2904294/1297603) — Yaroslav, Aug 06 '13 at 13:03
Instead of using `PRINT` use `SELECT`. Your `CONVERT` is implicitly changing the output format of the datetime. Now, if the timestamp is the number of seconds since 1970-01-01, why do you need the milliseconds to be output? — Aaron Bertrand, Aug 06 '13 at 13:06

score 0 · Answer 1 · answered Aug 06 '13 at 13:12

You get the milliseconds by adding the third parameter, e.g. like this:

Convert(varchar(100), @myDate, 121)

See http://technet.microsoft.com/en-us/library/ms187928.aspx for details.

For the timezone have a look at the DATETIMEOFFSET datatype: http://technet.microsoft.com/en-us/library/bb630289.aspx

SQL server convert 13 char long unix date strings into datetime data type

1 Answers1