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.
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:
- SQL SERVER – Puzzle – Playing with Datetime with Customer Data
- SQL SERVER – Alternate to AGENT_DATETIME Function
- SQL SERVER – Adding Datetime and Time Values Using Variables
- SQL SERVER – Puzzle – Inside Working of Datatype smalldatetime
- SQL SERVER – Find Weekend and Weekdays from Datetime in SQL Server 2012
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
