SQL SERVER – Script to Convert Date to Julian Dates

There are many methods to store date values. One of them is Julian date. It is a seven digit number where first four is the year part and the next three digits are the number of days from Jan 01.

If you want to convert this number into a valid datetime value, you can use the following simple method

DECLARE @JULIANDATE INT
SET @JULIANDATE= 2012146
SELECT DATEADD(DAY,@JULIANDATE%1000-1,DATEADD(YEAR,0,LEFT(@JULIANDATE,4))) AS JULIANDATE

The logic is to create Jan 01 of year part and add last three digits as day part using the DATEADD function

The result is

JULIANDATE
 -----------------------
 2012-05-25 00:00:00.000

Do let me know if you know any other method to convert dates in Julian Dates. I will be happy to publish that on this blog.

SQL SERVER - Script to Convert Date to Julian Dates juliandate-800x300

Additional note: This should not to be confused with the Julian calendar, a Julian date or day number is the number of elapsed days since the beginning of a cycle of 7,980 years invented by Joseph Scaliger in 1583. The purpose of the system is to make it easy to compute an integer difference between one calendar date and another calendar date.

Here are few other relevant blog post on this subject:

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – FIX: SQL Server Not Starting – Msg 864, Level 16, State 1- Buffer Pool Extension Feature
Next Post
SQL SERVER – Security Conversations and Notes with a DBA

Related Posts

Leave a Reply

Menu