Monday, May 3, 2010

Convert Julian Date YYYYDDD to Datetime Format

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:
 

8 comments:

  1. how to we validate if given integer is valid Julian date.

    ReplyDelete
  2. 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

    ReplyDelete
  3. 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



    ReplyDelete
  4. 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

    ReplyDelete
  5. 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.

    ReplyDelete