How can we convert the time portion(HH:MM:SS) of a julian date to normal date time in sql server?

Question

I have a column in my table-TimeUpdated but the data coming to this column from source is in Julian datetime format.

I found this below query to convert the Date part of julian timestam to normal timestamp -

 declare @a as int = 2458228
 select @a, dateadd(d,@a - 2440588,'1970-01-01')

But i can't figure out how to convert the Hour-Minute-second part. Can anybody help? Thanks in advance.

P.S. - 2458228.05929 in Julian means 19-04-2018 18:55:20 IST. Also you can refer to this site which will convert any day to Julian Datetime - http://www.onlineconversion.com/julian_date.htm

well, that returns `NULL` as is since you aren't setting @a to anything — S3S, Apr 19 '18 at 13:41
@scsimon yes I forgot to add that.I am passing 2458228 as the value of 'a' and that part is working fine. I am concerned about the decimal part. — Be1ng_Kr1Sh, Apr 19 '18 at 16:31
Brain dead. I cannot think of a good reason to do so, except “I’m too lazy to update it.” — duffymo, Apr 19 '18 at 17:03
Julian dates have very little to do with the Julian calendar. Julian dates are a variant of a system created by historian Joseph Scaliger in 1583 as a common system into which he could convert the various calendars he had to deal with. Virtually any calendar can be converted into Julian dates. — Gerard Ashton, Apr 20 '18 at 08:59

score 2 · Accepted Answer · answered Apr 19 '18 at 13:50

If I am understanding correctly, then the correct expression would be:

DECLARE @a int = '2440588';
DECLARE @Julian decimal(16,5) = 2458228.05929;

SELECT DATEADD(DAY,CONVERT(int,@Julian) - @a, '19700101') +
       DATEADD(HOUR,12,DATEADD(SECOND,(60 * 60 * 24) * (@Julian - convert(int,@Julian)),0));

This gives the UTC time 2018-04-19 13:25:22.000 (which, if converted to IST (Indian Standard Time (UTC +5:30) for those who don't know their Timezones) is 2018-04-19 18:55:22.000.

How can we convert the time portion(HH:MM:SS) of a julian date to normal date time in sql server?

1 Answers1