How to convert 13 digit binary code into DateTime format in MSSQL

Question

I am trying to insert 13 digit binary code to Datetime format,I have tried some examples in stackoverflow but nothing is worked for me .

Note : got the 13 digit binary value from API as a input parameter

this is my binary code

"1520503133980"

this is the required format "08-03-2018 03:28:53" to insert into the column "SESSION_START_DATE"

this is my insert query

INSERT INTO [dbo].[EC_USER_SESSION] (IPADDRESS,SESSION_START_DATE,SESSION_END_DATE,SESSION_STATE,CREATED_BY,CREATED_DATE)
            VALUES(@IPADDRESS,@SESSION_START_DATE,@SESSION_END_DATE,@SESSION_STATE,@CREATED_BY,@CREATED_DATE)

I found this solution in stackoverflow

DECLARE @b bigint = 1520503133980
SELECT DATEADD(ms, @b % 1000,DATEADD(ss, @b/1000, CONVERT(DATETIME2(3),'19700101')))

output is :

2018-03-08 09:58:53.980

I want the output in the above mentioned format

But I don't know that how to apply this solution in my Insert Query.

"I want the output in the above mentioned format". [DataTime have no display format.](https://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028) — Zohar Peled, Mar 08 '18 at 13:46

score 0 · Answer 1 · answered Mar 08 '18 at 11:54

Following code will be helpful to you,

 DECLARE @SESSION_START_DATE datetime
 SET @SESSION_START_DATE = DATEADD(ms, 1520503133980 % 1000,DATEADD(ss, 1520503133980/1000, CONVERT(DATETIME2(3),'19700101')))
 INSERT INTO EC_USER_SESSION(SESSION_START_DATE) VALUES(@SESSION_START_DATE);

Sql Fiddle Demo here

How to convert 13 digit binary code into DateTime format in MSSQL

1 Answers1