Julian to calendar

Question

Post reply

Julian to calendar

Jon Sauer

SSC Enthusiast

Points: 105
More actions
December 12, 2005 at 3:26 pm

#90094

Hi - I'm looking to convert two styles of julian dates:
jjjy for instance: 3645 = '2005-12-30'
yjjj for instance: 5364 = the same
Does someone have a code function to handle these?
Thanks!
Jon

Viewing 5 posts - 1 through 5 (of 5 total)

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

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

Will the days always be 3 digits? (0015 for January 1, 2005 as opposed to 15). What happens in 2010?

Making some assumptions, this might be a place to start:

declare @x varchar(4)

declare @year int

declare @monthday int

declare @newdate varchar(10)

declare @newdt datetime

set @x = '3645'

select @year = convert(int,substring(@x,len(@x),len(@x)-3)) + 2000

select @monthday = convert(int,substring(@x,1,3)) -1

select @newdate = '01/01/' + convert(varchar(4),@year)

select @newdt = convert(datetime,@newdate)

select convert(varchar(12),dateadd(dd,@monthday, @newdt))

The above could probably be compresssed into a smaller number of operations. View expanded here for clarity.

HTH

And then again, I might be wrong ...
David Webb

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 2

Jon,

David is correct... since Julian dates recycle every 10 years, should we assume that you will never have a future dated Julian date?

Also, would you rather (recommended) have the date converted to a datetime datatype or formatted exactly as you listed?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jon Sauer SSC Enthusiast Points: 105 More actions · Answer 3

Thanks David!

For my purposes, the recycling is not an issue. Ultimately, this is for calculating expiration dates, and if product is greater than 9 yrs old (for instance), I'll need to check for that as well.

I would rather have the date converted to datetime datatype, yes.

Thanks for the help!

Jon Sauer SSC Enthusiast Points: 105 More actions · Answer 4

I'm also getting:

Server: Msg 242, Level 16, State 3, Line 15

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.