convert julian date to standard date format

Question

convert julian date to standard date format

schauhan13

SSC Eights!

Points: 901
More actions
August 27, 2009 at 2:05 pm

#385269

in my table there is clumn
Jdate int
In this column julian date is stored
now i need to convert it into standard date. Can anyone help me please

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

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

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

Please define what you mean by Julian Date. Sorry, but I have seen systems that have used that term and the Julian Date was a home grown representation.

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

schauhan13 (8/27/2009)
in my table there is clumn
Jdate int
In this column julian date is stored
now i need to convert it into standard date. Can anyone help me please

At least provide some samples of what you call Julian dates. Like Lynn said, there are a lot of things that people call a Julian date.

--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)

schauhan13 SSC Eights! Points: 901 More actions · Answer 3

we have db2 database in our company for as/400. in that we hav julian date column.

for example julian date for 9/9/2009 is 109252

set @sdate = (SELECT DATEADD(dd, CONVERT(int, RIGHT(@jdate, 3)) - 1, CONVERT(datetime,SUBSTRING(@jdate,1,2)+'0101', 212)))

this query gives me standard date from julian date but the problem is my column is integer and above query uses string operation for jdate!

Ken McKelvey SSCoach Points: 18856 More actions · Answer 4

DECLARE @jdate int

SET @jdate = 109252

SELECT DATEADD(d, @jdate - 69189, 0)

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 5

Ummmm... won't that offset number work only for 2009?

--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)

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

Can you provide a sample of a julian date for a date in 2008? I have an idea on how it is encoded.

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

If I am right, this should work:

DECLARE @jdate int

SET @jdate = 109252

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

Lowell SSC Guru Points: 323518 More actions · Answer 8

I'd seen this before;

here's how i've done it, and i broke it down to help everyone understand.

the way it works is the first 3 digits is the century julian offset, and the last 3 are the day offset:

109252

--date is 01/01/1900 + 109 years + 252 days(minus one day)

set nocount on

DECLARE @sdate int

SET @sdate = 109252

--date is 01/01/1900 + 109 years + 252 days

--base date

select convert(datetime,'01/01/1900')

-- the right year

select dateadd(year,@sdate /1000,convert(datetime,'01/01/1900'))

-- almost the right date

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

--the right date, offset of one day

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

/*results

-----------------------

1900-01-01 00:00:00.000

-----------------------

2009-01-01 00:00:00.000

-----------------------

2009-09-10 00:00:00.000

-----------------------

2008-09-09 00:00:00.000

*/

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!

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 9

Lynn Pettis (8/28/2009)
If I am right, this should work:
DECLARE @jdate int
SET @jdate = 109252
select dateadd(dd, (@jdate - ((@jdate/1000) * 1000)) - 1, dateadd(yy, @jdate/1000, 0))

That's more like it... and the same day in 2010 would be 110252. Because of the leap year in 2008, the same day in 2008 would be 108253.

For those interested, the format of this type of Julian date is CYYJJJ where:

C is the number of whole centuries since 1900-01-01. Any date between 2001-01-01 and 2099-12-31 will have a C value of 1.

YY is the 2 digit year

JJJ is the day number of the given YY year. This number can be determined using (for example) DATEPART(dy,'2009-09-09')

--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)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 10

This seems to do the job:

select

JD,

DT = dateadd(dd,(JD%1000)-1,dateadd(yy,JD/1000,0))

from

( -- Test Data

Select JD =108366 union all

select JD =109252

) a

Results:

JD DT

----------- -----------------------

108366 2008-12-31 00:00:00.000

109252 2009-09-09 00:00:00.000

Edit: Modified original post to remove a bug for last day of a leap year.