Julian Date

building semantic layer
1

Hi All,
I know that my question is posted before :oops: but it was for ORACLE but I’m creating a universe based on db2/400 and all dates are formatted as a Julian dates (6 and 7 digits) and I need to convert it the normal date format mm/dd/yyyy or dd/mm/yyyy. I tried to use the functions which used in CR to convert the Julian dates but it didn’t work with the Universe!!!
any help will be highly appreciated as I’m using BO-XI.

Mahmoud :egypt: (BOB member since 2005-03-22)

2

Hi there!

I did a quick search and it appears that DB2 databases use SQL syntax.

The beginning of the Julian calendar is midday on January 1st, 4713 BC. I’ve managed to get the result provided below to spit out the same result as calculators available on the public internet. Some such sites are:
http://aa.usno.navy.mil/data/docs/JulianDate.html
http://wwwmacho.mcmaster.ca/JAVA/CD.html
http://www.tesre.bo.cnr.it/~mauro/JD/

So just create a new date object in Designer with the code given below:

tablename.juliandate-1721423.5-693596+convert(datetime, '1899-12-31')

Explanation of figures:

There are 1721423.5 days involved in the “BC” era (from midday January 1, 4713 onwards.) This is assuming year 1 BC, year 5 BC, year 9 BC etc are leap years.

After that, there are 693596 days prior to January 1, 1900 with every year that is a multiple of 4 being a leap year, except for years that are a multiple of 100 but are not a multiple of 400. (eg the year 800 is a leap year but the year 900 is not).

** Also note that the results the internet calculators (and thus this formula) give might be a bit dubious due to inconsistencies in the definition of leap years prior to year 0005. Refer to this website for further details:
“What years are leap years?” section on page

Hope this helps you!
:crazy_face:

jac :australia: (BOB member since 2005-05-10)

3

I see on another thread that in DB2 you can’t just add numbers, but must say you are adding days for example. Refer Predefined condition with Rolling Date prompt

Anyway, the code I provided would therefore need to be something like this:

convert(datetime, '1899-12-31') + tablename.juliandate days -1721423.5 days -693596 days

jac :australia: (BOB member since 2005-05-10)