Convert Julian date to Gregorian date

Question

Post reply

Convert Julian date to Gregorian date

pinky

SSCommitted

Points: 1997
More actions
May 27, 2010 at 8:13 am

#232887

I am trying to convert Julian dates to gregorian dates in sql server but Julian date column has invalid dates EX: X0068, so my sql is giving the below error.
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'X0068' to data type int.
SELECT juliandate
, dateadd(dd, [juliandate] % 1000, '12/31/' + cast([juliandate] /1000 +1900 - 1 as varchar(4))) as greg
FROM [dbo].[juliantest]
Can anyone let me know how to avoid the error?
Thanks for your help

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

pinky i guess the question is what do you want to do if the date is invalid?

return NULL?

SELECT

juliandate,

CASE

WHEN juliandate LIKE [^0-9]

THEN NULL

ELSE dateadd(dd, [juliandate] % 1000, '12/31/' + cast([juliandate] /1000 +1900 - 1 as varchar(4))) as greg

END

FROM [dbo].[juliantest]

pinky (5/27/2010)
I am trying to convert Julian dates to gregorian dates in sql server but Julian date column has invalid dates EX: X0068, so my sql is giving the below error.
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'X0068' to data type int.
SELECT juliandate
, dateadd(dd, [juliandate] % 1000, '12/31/' + cast([juliandate] /1000 +1900 - 1 as varchar(4))) as greg
FROM [dbo].[juliantest]
Can anyone let me know how to avoid the error?
Thanks for your help

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

pinky SSCommitted Points: 1997 More actions · Answer 2

Thank you so much Lowell. That worked.

But i have another problem here, Julian date 04126 is getting converted to 1904-05-05 00:00:00.000 instead of 2004-05-05. Can you please let me know how to fix this.

Thanks

Lowell SSC Guru Points: 323504 More actions · Answer 3

i have a different conversion in my notes: what format are your dates actually in?

DECLARE @jdate int

SET @jdate = 109252

--in AS400/DB2 date is 01/01/1900 + 109 years + 252 days

select dateadd(day,@jdate % 1000,dateadd(year,(@jdate /1000) -1,convert(datetime,'01/01/1900')))

or

select dateadd(dd, (@jdate - ((@jdate/1000) * 1000)) - 1, dateadd(yy, @jdate/1000, 0))

======edit===========

because your date appears to be 5 chars, 04 and 215 for the year > 2000 plus the number of days, this formula works for me:

--04216

DECLARE @jdate int

SET @jdate = 04216

--in this format, assuming nothing prior to 2000 exists, date is 01/01/2000 + 04 years + 216 days

select dateadd(day,@jdate % 1000,dateadd(year,(@jdate /1000) ,convert(datetime,'01/01/2000')))

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

pinky SSCommitted Points: 1997 More actions · Answer 4

I have dates in Julian YYDDD format. The sql below is not fetching the correct output. I have data prior > and < 2000

select dateadd(dd, (04126 - ((04126/1000) * 1000)) - 1, dateadd(yy, 04126/1000, 0))

04126 = 1904-05-05 00:00:00.000

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

You'll need to test the Year portion and decide if it is in 1900's or 2000's. You will need to review your data to determine the appropriate cutoff. It could be if YY > 50 then add 1900 else add 2000.

pinky SSCommitted Points: 1997 More actions · Answer 6

pinky

SSCommitted

Points: 1997

May 27, 2010 at 1:18 pm

#1173556

Got it!!!

Thank you so much Lynn.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Lynn Pettis

SSC Guru

Points: 442467

May 27, 2010 at 1:28 pm

#1173559

You're welcome.

Paul White SSC Guru Points: 150467 More actions · Answer 8

Good to know the Y2K problem is still alive and well 😀

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SparTodd Right there with Babe Points: 717 More actions · Answer 9

I was trying to solve the same problem this morning. Being very much a rookie, I searched for help and came across this thread. In my case, my Julian date column (OLDCLM) is only for characters (YJLN), and the table only contains data for a rolling 10 years.

SELECT OLDCLM,

CASE SUBSTRING(OLDCLM, 1, 1)

WHEN '0' THEN DATEADD(day, CAST(SUBSTRING(OLDCLM, 2, 3) AS INT) -1, '2010-01-01')

WHEN '9' THEN DATEADD(day, CAST(SUBSTRING(OLDCLM, 2, 3) AS INT) -1, '2009-01-01')

WHEN '8' THEN DATEADD(day, CAST(SUBSTRING(OLDCLM, 2, 3) AS INT) -1, '2008-01-01')

WHEN '7' THEN DATEADD(day, CAST(SUBSTRING(OLDCLM, 2, 3) AS INT) -1, '2007-01-01')

WHEN '6' THEN DATEADD(day, CAST(SUBSTRING(OLDCLM, 2, 3) AS INT) -1, '2006-01-01')

WHEN '5' THEN DATEADD(day, CAST(SUBSTRING(OLDCLM, 2, 3) AS INT) -1, '2005-01-01')

WHEN '4' THEN DATEADD(day, CAST(SUBSTRING(OLDCLM, 2, 3) AS INT) -1, '2004-01-01')

WHEN '3' THEN DATEADD(day, CAST(SUBSTRING(OLDCLM, 2, 3) AS INT) -1, '2003-01-01')

WHEN '2' THEN DATEADD(day, CAST(SUBSTRING(OLDCLM, 2, 3) AS INT) -1, '2002-01-01')

WHEN '1' THEN DATEADD(day, CAST(SUBSTRING(OLDCLM, 2, 3) AS INT) -1, '2001-01-01')

END AS DATE_CREATED,

SUBSTRING(OLDCLM, 2, 3) AS JLN

FROM MDMBILL..CLAIMS

ORDER BY OLDCLM

Maybe not the best solution, but it got the job done as try to modernize this database (which has its roots in the 1980s) so it can hold more than 10 years of data.