JulianDate

Syntax

JulianDate ( date )

Parameters

date

A number representing the input date between January 1, 1970 and Dec 31, 2037. The number is the number of seconds elapsed since midnight, January 1, 1970. To retrieve this number, use any of the following functions: Today(), TodateEx(), GetFirstDate(), GetLastDate(), DateRoll().

Date-Time type attribute properties of a member can also be used to retrieve this number. For example: Product.currentmember.[Intro Date] returns the Introduction or release date for the current product in context. [Cola].[Intro Date] returns the Introduction or release date for the “Cola” product.

Notes

This function is useful in converting the UNIX date to Julian Date or the 1900 Date system recognized by Microsoft Excel.
In the 1900 date system, the first day that is supported is January 1, 1900. When you enter a date, the date is converted into a serial number that represents the number of elapsed days since January 1, 1900. For example, if you enter July 5, 1998, Microsoft Excel converts the date to the serial number 35981. By default, Microsoft Excel for Windows uses the 1900 date system.

Return Value

This function returns juliandate, a number representing the Julian date. This number is a continuous count of days and fractions elapsed since noon Universal Time on January 1, 4713 BC in the proleptic Julian calendar.

Note:

For Excel workbooks using 1900 date system, (JulianDate – 2415018.50) gets the sequential serial number as per 1900 date system.

Example

The following query returns the total monthly sales for all Colas along with their release dates as in 1900 Date system in market “California” for “March 2007.”


WITH MEMBER 
 Measures.[Product Intro Date] 
AS 
 'JulianDate(Product.CurrentMember.[Intro Date]) – 2415018.50'
SELECT
 {Measures.[Product Intro Date], Measures.Sales}
ON COLUMNS,
 {Colas.Children}
ON ROWS
FROM Sample.Basic
WHERE 
 (California, [March 2007], Actual);