Julian date to regular date

Transact-SQL
1

Hello SQL expert,

How do I convert Julian date (ex: 731611) to regular date in sql?

Thanks guys

2

Please try this sample code it may be helpful to you.

set nocount on
        DECLARE @sdate int
        SET @sdate = 109252
        --date is 01/01/1900 + 109 years + 252 days
        --base date
        select convert(datetime,'01/01/1900')
         -- the right year
        select dateadd(year,@sdate /1000,convert(datetime,'01/01/1900'))
         -- almost the right date
        select dateadd(day,@sdate % 1000,dateadd(year,@sdate /1000,convert(datetime,'01/01/1900')))
        --the right date, offset of one day
        select dateadd(day,@sdate % 1000,dateadd(year,(@sdate /1000) -1,convert(datetime,'01/01/1900')))
        /*results
        -----------------------
        1900-01-01 00:00:00.000
        -----------------------
        2009-01-01 00:00:00.000
        -----------------------
        2009-09-10 00:00:00.000
        -----------------------
        2008-09-09 00:00:00.000
        */
3

what does those numbers represent in regular date ?

4

is that not 16th November 1973

5

@mole999, If it was it would not be a Julian date.
@Mel, @khtan has a good point. you will need to plug the regular date into @jason_clark 's code to see if that works.

6

Refer some methods here http://beyondrelational.com/modules/2/blogs/70/posts/14979/converting-julian-date-into-date.aspx

7

mea culpa
Sunday 16 January 2710, had my familiar look head on

1 Like
8

i don't know. If 731611 is that date, than it is not julian date.

But If that date is 16th Nov 1973, than isn't it just simply date in YYDDMM ?

9

I was wrong in terms of the 'Julian date', but then again I can't see anybody working with 700 years in the future YYDDMM would do it for me

Maybe we will all find out some time

10

@mole999, you are correct...it is December 24, 2710. That's away to far....thanks everyone !