Considering that even MS has used integer dates and times in MSDB, you'd think they'd have some native functionality that would more easily take care of such things.

Nicely done on avoiding conversions to strings on this. They really can lend to a performace problem.

As a side bar, you're good formula can be simplified a wee bit.

declare @julian_date int = 2012060
select dateadd(year,@julian_date/1000-1900,-1)+@julian_date%1000

For those still using 2005, same thing... just a different test setup.

declare @julian_date int 
SELECT @Julian_Date = 2012060
select dateadd(year,@julian_date/1000-1900,-1)+@julian_date%1000

Heh... seriously??? I mispelled "times" as "timesd", corrected it, and that drives the post into moderation? Seriously? ;-)

Jeff Moden

Thanks for the feedback. Indeed %1000 is not needed.

You bet, ol' friend.

BTW... I just wanted to say "thank you" for all you've done for folks over the years on forums and in your blogs. You've definitely one of the good guys and I'm proud just to know you. Thanks, Madhivanan!

Jeff Moden

Thanks for your kind words. I feel very happy when I get such a feedback from you. You are the legend with so much of experience in the technology. Considering your vast experience I am very happy to hear feedbacks from you. Whenever there is a feedback from you I can gaurantee that someone will learn something new. I have learnt a lot from you right from where I came to know your knowledge in belutions.com Your posts on avoiding RBAR, clever usage of Tally number table, Quirky update technique and Running total are very popular that are referenced everywhere. You have very significant contributions to SQL Server. Keep up your good work.

Look to the West and you'll see the glow from me blushing. Thanks Madhivanan.