Converting Date to Julian Date

[tgtfranz]

I have a date that I am wanting to convert to a Julian date to match up with another field that is stored in another table. I am having some problems seeing how this function from Novick Software works.


CREATE FUNCTION dbo.udf_DT_ToJulian (

@Date datetime -- Date to convert to Julian
) RETURNS int -- Julian date. # days since 1900-01-01
WITH SCHEMABINDING -- This is a deterministic function.


My field is employees_class.postion_start_date in a datetime format. This is what I need to convert to Julian.

Any help is much appreciated.
Thanks

 

[SQLDenis]

today would be 39377

declare @d datetime
select @d = '20071024'

select datediff(dd,0,@d)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[tgtfranz]

Ok, I can see how this converts todays date to Julian but what about an existing database field that is used in a view?

Something like this???

declare @d datetime
select @d = employees_class.position_status_date

select datediff(dd,0,@d)

 

[gmmastros]

No. Like this...

[tt][blue]
select datediff(dd,0,[!]employees_class.position_status_date[/!])
[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PDreyer]

For your info

udf_DT_ToJulian -- Julian date. # days since 1900-01-01

A better name would be udf_DT_ToSqlDay
MS Sql day 0 is 1900-01-01
Julian day 0 is -4713-11-24 if we project the current Gregorian dating system back beyond the time of its implementation (-4712-01-01 Julian Calendar).
So MS Sql day 0 is Julian day 2415021

And to get the Julian day number as a decimal value (with time as a decimal fraction) e.g.
select convert(float, position_status_date)+2415020.5

And it shouldn't really be referred to as Julian date as that implies a date on the Julian calendar.

 

[r937]

julian date" also sometimes refers to day-within-year, i.e. YYYYDDD

so today would be 2007298

r937.com | rudy.ca

 

[PDreyer]

Yes, pity they didn't call it Ordinal Date

Ordinal Date Formats
The day number within a given year can be expressed as:

"1993045" or "1993-045" (complete representation)
"93045" or "93-045"
"-045"

 

[tgtfranz]

Ok,I have had a chance to play around with this for a while and it revealed even more that is needed to do a valid comparison.

I have a wageclass that is made up of a number that looks like this

024*0*10044

024 is the class
*0* is placeholder
10044 is the julian date based on fiscal year.

Our fiscal year is 07/01 to 6/30

I have a date of 08/25/2000 that I need to covert to Julian. So the date that I would need to convert to Julian is 07/01/2000.

I need to use this date as there is nothing else in the table that I can join it up to and get the results that I need.

I am not sure if using this funtion mentioned above is what I want to use and I do not see how the other functions will help either.

Thanks for any assistance that you can give.

 

[PDreyer]

Since you do not agree with the accepted definition of Julian date you need to explain what 10044 mean.

Is it the number of days (27.5 years) since Epoch?

select datediff(dd,Epochal_date,position_status_date)