Odd Julian Date to Gregorian / Calendar Date 1

[jimoo]

I have some data that came from AS/400 DB2 and it contains julian date data that is described as INT (7) and I need to convert it to a normal calendar date (gregorian).

Here is a format of the data. This would represent 10/27/1960

1960301 - should be converted to 10/27/1960
1977308 - should be converted to 11/4/1977

Year is 1st four digits.

I have tried several of the examples on this forum. No luck.

Ideally, I'd like for the conversion to be command line or function.

Thanks in advance for your assistance.





Jim

 

[gmmastros]

Can you show a sample that occurs in January of any year?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

It looks to me like the first 4 digits represents the year and the last 3 digits represents the day of the year. If so, then take a look at this...

Declare @TestDate Int;

Set @TestDate = 1960301;

Select DateAdd(Day, @TestDate % 1000 - 1, DateAdd(Year, (@TestDate / 1000) -1900, 0))

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jimoo]

gmmastros, excellent. This is exactly what I am looking for. I had found several variations but none of them worked, but your example did. I even tested for leap years and it worked perfectly.



Jim