Convert Date to Julian Date

[kcirino]

I need to convert a standard date 7/15/2009 to a julian date example: 109196
is the julian date for 7/15/2009. Is there a formula I can use in Access to
do this through SQL?

 

[Jerry Whittle]

DatePart("y",#7/15/2009#) will return 196.

DatePart("yyyy",#7/15/2009#) will return 2009.

Year(#7/15/2009#) will also return 2009

In your terminology, what is the julian date for 7/15/1996? 7/15/2019?

 

[John Spencer]

DatePart("y",SomeDate) returns the number of the day in the year

DatePart("yyyy",SomeDate) returns the year

DatePart("yyyy",SomeDate) mod 100 will strip off the century

so

DatePart("yyyy",SomeDate)\100 will return the century
so

(DatePart("yyyy",SomeDate)\100) - 19 will return 0 for the 20th century
and 1 for the 21st century.

Combine all that and make sure things are type cast correctly.
?(CLng((DatePart("yyyy",SomeDate)\100)-19) * 100000)
+(CLng((DatePart("yyyy",SomeDate)) mod 100)* 1000) +DatePart("y",SomeDate)

And your probably don't need the Clng to force the type casting


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================