Convert Julian Date YYYYDDD to Datetime Format

Posted by VIJI Monday, May 3, 2010

Convert YYYYDDD to datetime,Convert Julian Date YYYYDDD to Datetime Format,Convert Julian Date YYYYDDD to Gregarian Format,SQL,SQL Query
I got a requirement to display the date which is the format 7-digit YYYYDDD (Julian Date) to display in Gregarian format.

This is the query I wrote to convert YYYYDDD format to YYYY-MM-DD format.

YYYY - 4 - digit Year
DDD -  number of the day in the year

SQL Query:

SELECT  [Julian Date], DATEADD(DAY, [Julian Date]% 1000 - 1, DATEADD(YEAR,


[Julian Date]/ 1000 - 1900, 0)) AS [Gregarian Date]


FROM dbo.Test
 
Sample Output:
 

5 Responses to Convert Julian Date YYYYDDD to Datetime Format

  1. Anonymous Says:
  2. how to we validate if given integer is valid Julian date.

     
  3. Hi Anonymous,

    I can suggest you several validations.

    1)Make sure the date length is 7

    2)Take the last three characters of the date value and check whether the value >= 001 and <= 365

    3)Also Values for datetime data types earlier than January 1, 1753 are not permitted in SQL Server. So check the year by taking first 4 characters.

    I hope it will help you

     
  4. Anonymous Says:
  5. This algorithm doesn't work after Feb 28 during leap years!

    Instead, try this:

    dateadd(d,convert(int,right(JulianDate,3))-1,convert(date,left(JulianDate, 4) + '-01-01'))

    Basically, get the year and convert to Jan 1 of that year, then add the number of days minus one.

    Scott Cameron
    scottca@live.com



     
  6. Anonymous Says:
  7. The above method fails with error "Conversion failed when converting date and/or time from character string." when I run against a large set of data with Julian date that needs to be converted.

    Somehow it executes when I run it against a smaller sample of data. Anyone encountered such problem? Thanks in advance

     
  8. hello sir I saw your blog and i think you have much idea about .net sql etc.sir i am a independent project developer and i need your help.it will be grateful if you will help me.
    i am waiting for your reply.
    my mail id sssatpathy191@gmail.com.
    thank you sir.

     

Post a Comment