Julian to Gregorian

[TheEntertainer]

Dear All,

I've tried to use the formula in thread183-1148071

Changing the table and the fields of course

select
dateadd(d,convert(int,MCD2J) % 1000,convert(datetime,
(convert(varchar,convert(int,MCD2J) /1000 + 1900) + '/1/1')))

From PS_TEST.TSDTA.F0006

I keep getting an error message of:

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

I've been told that the date field is Julian, which is why I'm using this formula although I'm not sure that it is as it a 9 digit number ie 131531017 = 09/01/2007. And most of the formulas I've found on the web use numbers around 6 or 7 digits in there example of converting Julian dates.

Any ideas

Thanks

 

[Catadmin]

What do you get when you run this by itself?

select (convert(varchar,convert(int,MCD2J) /1000 + 1900) + '/1/1')

It might be you need to add zeros in the /1/1 part...



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"