Share via

Convert Julian dates in Normal date

Vineet S 1,070 Reputation points
2024-09-19T18:36:21.3566667+00:00

How to convert Julian date column in normal date like colum contains 12490,12492 Julian dates

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,998 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,591 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,656 questions
Accepted answer
  1. LiHongMSFT-4306 28,041 Reputation points
    2024-09-20T01:33:08.86+00:00

    Hi @Vineet S

    As far as I know, the Julian date is format as YYDDD, where YY means the two-digit form of the year and DDD means the DDDth day of that year.

    Just wondering what normal date you want to convert from 12490, because 490 clearly exceeds the maximum number of days in a year.

    Best regards,

    Cosmog

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Bruce (SqlWork.com) 66,706 Reputation points
    2024-09-19T22:50:15.0466667+00:00

    simple expression:

    declare @j int = 24263; -- 2024-09-19 in Julian
select  dateadd(dy, (@j % 1000) - 1, dateadd(yy, @j/1000, cast('2000-01-01' as date)))
    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.