Convert JulianDate colum to normal 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

Transact-SQL (2000)

Convert JulianDate colum to normal date

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-11-16 : 16:23:36

I have read some thing online that show me how to convert a julian date to a normal date. My question is how can I show an entire column converted to normal date. I have about 20,000 rows and I want to convert the julian date to a normal date, and am not sure how to do it on a column?
This was what I had read

http://www.novicksoftware.com/UDFofWeek/Vol2/T-SQL-UDF-Vol-2-Num-3-udf_DT_FromJulian.htm

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-16 : 16:26:51

Create the function from that article, then
SELECT dbo.udf_DT_FromJulian(yourjuliancolumn) AS UDFDate FROM yourtable

Or add a new datetime column to you table and
UPDATE yourtable SET datetimecolumn = dbo.udf_DT_FromJulian(yourjuliancolumn)

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-11-16 : 16:34:27

Perfect, thanks for the quick reply, except the year is all messed up:

converted date JULDATE
8615-12-16 00:00:00.000 | 2452952
8616-11-20 00:00:00.000 | 2453292

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-16 : 17:37:40

Ha! The article you referred to doesn't use a true Julian date, it uses a "SQL Server corrected" Julian date because SQL Server datetimes cannot represent the full range of a true Julian date.

Here's a function that will work with a true Julian date, note that it will fail with an overflow error if you give it a Julian date that is outside the date range that datetimes support (January 1, 1753 through December 31, 9999)

create function udf_DT_FromJulian(@aJulianDate int)
returns datetime
as
begin
	return dateadd(day, @aJulianDate - 2361331, cast('17530101' as datetime)) 
end

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-16 : 19:58:10

The F_TABLE_DATE function on the link below contains a column for both the Julian Date and the Modified Julian Date. However, I think what is being presented in that link does not meet the definition of 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."

The ordinal date is also available in Function F_TABLE_DATE as column YEAR_DAY_OF_YEAR.

Date Table Function F_TABLE_DATE
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources