Convert A Field (Date) In A Table To Julian Date

[lhooker]

How can I convert a field (with a date) to a Julian date in a query (SQL) ?

 

[plog]

What's a Julian date? The number of days from 1/1/4713 BC? If so, I would use DateDiff() https://www.techonthenet.com/access/functions/date/datediff.php

Pretty sure it won't work that far back or on BC, so you would need to manually calculate what the Julian Date of 1/1/2000 (or some other reference date in the past), then add that to the DateDiff of 1/1/2000 and your date-->

JulianDate: DateDiff("d", [YourDateField], CDate("1/1/2000")) + 2372500

 

[isladogs]

Here is today's history lesson though it doesn't directly answer your question

The Julian calendar dates back to the time of Julius Caesar and was used across most of Europe for many centuries. Unfortunately it didn't quite allow for the actual time taken for the earth to go round the sun so gradually in became more & more inaccurate.

As a result, the Gregorian calendar was introduced in the time of Pope Gregory III but not in all countries at the same time

Much of Europe changed in 1582 but some countries like the UK didn't like these new fangled 'foreign' ideas and delayed the transition.
The UK was I believe one of the last & didn't change until 1752.
When the UK finally did so 11 days were 'lost' form the calendar leading to the 'Give Us Our Eleven Days' calendar riots of 1752.

See https://www.historic-uk.com/HistoryU...r-eleven-days/

Which means dates prior to 1582 can be converted fairly easily & those after 1752 may be OK. In between those dates is more of an issue and it seems for example that Access & SPSS use different criteria for determining the correct dates between 1582 and 1752.

As to how you convert 'correctly', you'll either need to write a conversion algorithm or use one done by someone else. It will depend on the country you are referring to. In reality there is no such thing as a 'correct' date.

This link may give what you need https://stevemorse.org/jcal/julian.html

Note that the UK wasn't last to change - for example Albania changed in 1912 & Turkey in 1927.
Sweden is a very 'interesting' example - this is from Steve Morse website

In an attempt to have a gradual conversion, Sweden decided not to have leap days from 1700 to 1740.
But after skipping leap day in 1700, they abandonded the plan.
This put them out of line with both the Julian and Gregorian calendars.
In 1712 they reverted back to the Julian calendar by having 30 days in Feb that year.

If nothing else, you may be able to use some of this info in pub quiz questions!

 

[plog]

You guys get the Wikipedia in England too?

 

[isladogs]

Yes though we're 11 days behind the USA ....

Actually I didn't look at Wikipedia for this response - I copied half of it from a similar answer I gave in this thread https://www.access-programmers.co.uk/forums/showthread.php?t=299157&highlight=Julian+calendar

I first learned about the Bristol calendar riots of 1752 when covering a history lesson whilst teaching in Bristol

 

[lhooker]

Plog,

I used the below format for my query. Do you see a problem with this ?

SELECT MyDate.Bill_Desc, MyDate.DeliveryOn, MyDate.Category, DateDiff("d",[DeliveryOn],CDate("1/1/2018"))*-1+1 AS JulianDate
FROM MyDate;

 

[arnelgp]

Public Function DateToJulian(dt As Date) As String
    DateToJulian = Right(Year(dt), 2) & Format(DateDiff("d", DateSerial(Year(dt), 1, 0), dt), "000")
End Function

on your query:

select [Date], DateToJulian([Date]) As Julian from yourTable;

 

[The_Doc_Man]

Julian date? If we are talking modern calendar terms, that is just the day number of the year and is thus something like this. I broke it down into steps so you could see what I was doing step-wise. Arnel's is probably quicker to type, but I am giving a more pedantic answer so you will see how it works.

ThisYear = CStr(DatePart( "yyyy", Date() ) )       'get year number
BOY = CDate( "1-Jan-" & ThisYear )                 'get date of beginning of year
JulianDate = 1 + ( Date() - BOY )                  'compute Julian date

If you did this for 2/2/2018, you would get 32 as an answer. The Julian date for Feb. 2nd, 2018 (Groundhog day!) is therefore 32/2018. Note however that for the year 2016 and 2017, if you were going after the Julian date for 3/3/year, you would get different answers. E.g. 3/3/2017 is 62/2017, but 3/3/2016 is 63/2016 due to leap year.

 

[plog]

You still haven't explained what a Julian Date is, so I don't know what kind of problem to look for.

Now, I am guessing that you want the Julian Day, which is the day number of year (e.g. 1/1->1, 1/2->2, 12/31->365). If that is the case you can use the DatePart function:

https://www.techonthenet.com/access/functions/date/datepart.php

JulianDay: DatePart("y", [YourFieldHere])

 

[lhooker]

Plog,

What I'm looking for is julian date format of yyddd.

 

[isladogs]

Clearly my explanation of the Julian & Gregorian calendars was utterly irrelevant to you !!!!

 

[lhooker]

No, just answering someone's question.

 

[The_Doc_Man]

I gave a partial answer earlier but you apparently either didn't see it or didn't understand it.

Public Function JDString( TheDate As Date ) As String

Dim ThisYear As String
Dim BOY As Date
Dim JulianDay As Long

ThisYear = CStr( DatePart( "yyyy", TheDate ) )       'get year number
BOY = CDate( "1-Jan-" & ThisYear )                 'get date of beginning of year
JulianDay = 1 + ( TheDate - BOY )                  'compute Julian date
JDString = Right$( ThisYear, 2 ) & Format( JulianDate, "000" )   'yyddd

End Function

If you want, you can use this in a query. To do so, make this public function part of a general module. Input will be a date (in Access Date format). Output will be "yyddd" which is your specified format.

Understand that there IS no Julian date functionality built-in for Access that I can find, and TECHNICALLY the format isn't really "yyddd" format because another format actually uses "ddd" for something else. By rights it should be "yyjjj" but that isn't a valid format for Access.

 

[Galaxiom]

What I'm looking for is julian date format of yyddd.

I do wish people would stop misusing the term "Julian Date". The meaning was defined over four centuries ago. All other use of the term is misuse.

What you want is properly called the "Ordinal Date".

If you just want the last two character of the year:

Right(Year(Date),2) & DatePart("y", Date)

 

[arnelgp]

Julian date is still in used.
We used it on work order while i was with the contractor of US army.

if the field you want to save is of Date type use this function
to convert your Julian date to Real date:

Public Function JulianToDate(dt As Variant) As Date
    dt = dt & ""
    JulianToDate = DateSerial(Left(Year(Date), 2) & Left(dt, 2), 1, Right(dt, 3))
End Function

to insert it in your table:

DoCmd.RunSQL "Insert Into yourTable (dateFieldName) SELECT JulianToDate(" & Me.txtJulian & ")"

 

[The_Doc_Man]

G., the U. S. Navy still uses Julian Date (called as such), meaning the ordinal day number of the year. Or they did when I retired in 2016.

Granted, not used that often - but definitely used as such, nonetheless.

 

[lhooker]

Arnelgp,

I'm receiving the below error for your recommended (as shown below) VBA statement. It appears that a reference is missing. Which one(s) is the statement looking for ?

Undefined function 'JulianToDate' in expression

DoCmd.RunSQL "Insert Into Bills (NextPaymentDate) SELECT JulianToDate(" & Bill_Due_Date & ")"

 

[arnelgp]

Put the functions i posted on a separate module in vba.

 

[Pat Hartman]

Access already has perfectly good functions to return "Julian" date as it is called in many mainframe systems. Let's not quibble about the accuracy of the name.

Format(YourDate, "yy") & DatePart("y", YourDate)

When working with dates, it is always best to use actual date functions rather than string functions. That way, you are not adversely impacted by your Windows date format or date strings that are not formatted as you expect such as 5/7/18 for May 7.

 

[lhooker]

Pat,

I tried your suggestion (shown below). Nothing is being returned. What am I doing wrong ? 'NextPaymentDate' is a textbox on the form.

Format(NextPaymentDate, "yy") & DatePart("y", NextPaymentDate)