Converting bigint to datetime in sqlserver

Question

I am trying to convert my bigint into a date and time. I have used the following link as a guidedance but it is not working as it is suppose to because from my understanding in this solution it is based on a millesecond.

Convert bigint to datetime

I have the following coding and have tried the following:


declare @starttime as bigint;
set @starttime = '2021021209295600000';

Select dateadd(HOUR, (@starttime / 100000) % (24 * 60 * 60),
               dateadd(day, (@starttime / 100000) / (24 * 60 * 60), '1970-01-01'))

The following error is being prompted:

The data types time and datetime are incompatible in the add operator.

Also as mentioned earlier I have tried the above mentioned solution however the following output is given which is incorrect select dateadd(s, convert(bigint, @starttime) / 1000, convert(datetime, '1-1-1970 00:00:00'))

Can someone help me please? The expected value is 2021-02-12 09:29:56

Thom A · Accepted Answer · 2021-02-12 10:08:43Z

1

This isn't pretty, but I would CONVERT it to a varchar, inject some of the needed characters, and then CONVERT to a datetime (as you have 5 decimal places, I assume it's actually meant to be a datetime2(5)):

DECLARE @starttime as bigint;
SET @starttime = 2021021209295600000;

SELECT CONVERT(datetime2(5),STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(CONVERT(varchar(20),@starttime),15,0,'.'),13,0,':'),11,0,':'),9,0,'T'),7,0,'-'),5,0,'-'));

answered Feb 12, 2021 at 10:08

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Annalise Azzopardi Over a year ago

thanks. I have even tried something similar but an error was prompted select convert(datetime, cast(@starttime/100000 as varchar(14)), 101) , however yours work

Thom A Over a year ago

Because '20210212092956' isn't a valid value either, @AnnaliseAzzopardi , it doesn't have any of the separators (such as - and :). That's what all those STUFF functions are doing; injecting the characters into the right place.

Annalise Azzopardi Over a year ago

and would it be possible to just select the date only instead of date and time. I have tried the following but an error is given: ,CONVERT(date,STUFF(STUFF(CONVERT(char(8),@starttime),7,0,'-'),5,0,'-')) as DateOnly

Thom A Over a year ago

Use a date, @AnnaliseAzzopardi .

Collectives™ on Stack Overflow

Converting bigint to datetime in sqlserver

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related