Convert Julian Date to Normal Calendar Date

[janice]

Hi,

I am having trouble converting a Julian date back to a normal calendar date.

If I get the Julian Day for today:

Code: Select all

dbadmin=> SELECT sysdate, julian_day(sysdate);
          sysdate           | julian_day
----------------------------+------------
 2013-03-22 09:04:29.667331 |    2456374
(1 row)

I can't convert it back with the TO_DATE function:

Code: Select all

dbadmin=> SELECT to_date(2456374, 'J');
ERROR 3457:  Function to_date(int, unknown) does not exist, or permission is denied for to_date(int, unknown)
HINT:  No function matches the given name and argument types. You may need to add explicit type casts

What gives?

[JimKnicely]

Hi Janice,

The TO_DATE function with the "J" format string work's as expected in Oracle!

Code: Select all

SQL> SELECT to_date(2456374, 'J') "Gregorian" FROM dual;

Gregorian
---------
22-MAR-13

And from what I've read in the "Template Patterns for Date/Time Formatting" section of the Vertica SQL Reference Manual, I would also think the above command should work to in Vertica! But as you seen from your experience, it does not

But you can do something like this:

Code: Select all

dbadmin=> SELECT TIMESTAMPADD ( 'd', 2456374 - 2415021, '01/01/1900')::DATE "Gregorian";
 Gregorian
------------
 2013-03-22
(1 row)

In general:

• TIMESTAMPADD ( 'd', :JULIAN_DATE - 2415021, '01/01/1900')::DATE "Gregorian";

I hope this helps!

[janice]

WOW, THANKS JIM!

[JimKnicely]

No problem!

FYI, check out the post "Converting to a Julian Date and Back Again!" here viewtopic.php?f=63&t=943!

[Julie]

@janice - What's a "normal" calendar date?