Convert Date to Julian Date

K

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?
 
J

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?
 
J

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
'====================================================
 
Top