Use Case Accelerators > How to Convert Gregorian Date to Julian Day Number

How to Convert Gregorian Date to Julian Day Number

Article #:53

Product:DMExpress

Version:8, 9

Summary

A Gregorian date can be converted to a Julian day number (defined as the integer day count since January 1, 4713 BC) in DMExpress by using the DateDiff function and the constant corresponding to the Julian day number on January 1, 1 AD based on the Gregorian calendar.

Resolution

The attached example demonstrates how to calculate the Julian day number (JDN) for a given Gregorian calendar date (GD) in DMExpress. It involves the following steps:

Create a number constant named Day1_JDN with a value of 1721426. This is the number of days from January 1, 4713 BC to January 1, 1 AD based on the Gregorian calendar.

Create a value named Day1_GD to represent the Gregorian date on January 1, 1 AD, as follows:

ToDate('00010101', 'YEARMM0DD0')

Create a value named <date_field>_to_JDN, where <date_field> is the Gregorian date field to be converted, defined as follows:

DateDiff(<date_field>, Day1_GD, 'day') + Day1_JDN

This calculates the day count from Day1_GD (January 1, 1 AD) to the given date, and then adds the previously defined constant, Day1_JDN, for the day count from January 1, 4713 BC to January 1, 1 AD, yielding the Julian day number for the given date.

You can then use the calculated Julian day number value in subsequent processing and/or outputs, such as the target reformat.

Note that there is a distinction between the Julian calendar and the Gregorian calendar when calculating the Julian Day Number. The value we use for the Julian Day Number on January 1, 1 AD, 1721426, is based on the Gregorian calendar. Calculators based on the Julian calendar will give a value of 1721424 for that date.

Additional Information

For details on creating values, see Expressions in the DMExpress help.