converting julian date to gregorian date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

converting julian date to gregorian date

Author

Topic

paragsomani
Starting Member

1 Post

Posted - 2007-07-17 : 07:29:48

I want to convert a julian date to gregorian calendar date?
How to do that?

e.g. Julian date 2007001 o/p : 01-01-2007 (dd-mm-yyyy)

Thanks in advance.

-Parag

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-17 : 07:36:06

[code]DECLARE @julian_date int

SELECT @julian_date = 2007001

SELECT gregorian = DATEADD(YEAR, @julian_date / 1000 - 1900, @julian_date % 1000 - 1)[/code]

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-17 : 09:35:33

quote:

Originally posted by khtan

DECLARE @julian_date	int

SELECT	@julian_date	= 2007001

SELECT	gregorian  = DATEADD(YEAR, @julian_date / 1000 - 1900, @julian_date % 1000 - 1)

That would give the wrong result on the last day of a leap year:

declare @julian_date	int
select	@julian_date	= 2004366

select gregorian = dateadd(year, @julian_date / 1000 - 1900, @julian_date % 1000 - 1)

select My_gregorian = dateadd(dd,(@julian_date%1000)-1,dateadd(year,(@julian_date/1000)-1900,0))


Results:
gregorian                                              
------------------------------------------------------ 
2005-01-01 00:00:00.000

(1 row(s) affected)

My_gregorian                                           
------------------------------------------------------ 
2004-12-31 00:00:00.000

(1 row(s) affected)

FYI:
This type of date is really the Ordinal Date, not the Julian Date.

http://en.wikipedia.org/wiki/Julian_Day
"The Julian day or Julian day number (JDN) is the number of days that have elapsed since 12 noon Greenwich Mean Time (UT or TT) on Monday, January 1, 4713 BC in the proleptic Julian calendar 1. That day is counted as Julian day zero. The Julian day system was intended to provide astronomers with a single system of dates that could be used when working with different calendars and to unify different historical chronologies.
...
...
The term Julian date is also used to refer to:
Julian calendar dates
ordinal dates (day-of-year)

The use of Julian date to refer to the day-of-year (ordinal date) is usually considered to be incorrect."

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-17 : 09:38:55

Thanks for the correction.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources