0

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

Annalise Azzopardi
  • 113
  • 1
  • 2
  • 13

1 Answers1

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,'-'));
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • 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 – Annalise Azzopardi Feb 12 '21 at 10:15
  • 1
    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. – Thom A Feb 12 '21 at 10:16
  • 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` – Annalise Azzopardi Feb 12 '21 at 10:37
  • 1
    Use a `date`, @AnnaliseAzzopardi . – Thom A Feb 12 '21 at 10:42