Julian date to Proper date

This function will convert Julian dates to proper dates

CREATE function julian_to_date(@julian varchar(25))
returns datetime
as
Begin
declare @date datetime

select @julian=substring(@julian,1,7)
if len(@julian)=7 and @julian not like '%[^0-9]%'
select @date=dateadd(day,cast(right(@julian,3) as int)-1,cast(left(@julian,4) as datetime))
else
select @date=null

return @date
end

Ex

Select
dbo.julian_to_date('2006128')


Madhivanan

Failing to plan is Planning to fail

Interesting and this onehttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762 too.

Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

You should be able to rewrite this from:


dateadd(day,cast(right(@julian,3) as int)-1,cast(left(@julian,4) as datetime))

to:


dateadd(day, (@julian % 1000) -1, LTRIM(@julian / 1000))


--
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de

Thanks Frank [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail