How to convert julian Date format to Oracle Date format

[ysrini9]

Hi All,

Could you please look into this

" How to convert julian Date format to Oracle Date format "

Thanks in Advance
srini

[loveojha2]

Did you try to use DATE.TAG transform function.

[balajisr]

Julian date of 1-JUL-06 is 2453918. To convert it into oracle date format:

SELECT TO_DATE(2453918,'J') from dual;

Is this what you want?

[chulett]

That's a real Julian date. Typically people mean an Ordinal date when they say Julian - the year plus the day number of that year.

That being said - there's no such thing as "Oracle date format". Oracle dates are stored in an internal representation and you can load and extract them from Oracle in pretty much any format.

What's important when loading is to make sure the format of the date and the format mask you are using match. And the best way to do (IMHO) that is to stick with ISO formatting, which is what the stages will automatically generate and consume.

My 'rules' are to stick with the Timestamp datatype when dealing with Oracle DATE fields. The OCI stages automatically generate SQL to extract or load the fields in the following format:

Code: Select all

YYYY-MM-DD HH24:MI:SS

I prefer to explicitly handle the 'time' portion of the date, even when that means adding a 'zero time' to those dates that don't "need" a time portion. This gives us full control over the DATE field and the stages never have a problem handling them. A 'standard' set of custom DATE handling routines helps everyone be consistant in their jobs.

[chulett]

How to convert julian Date format to Oracle Date format

The specific answer depends on whether this is in fact a Julian date or an Ordinal date. That will drive the flavor of IConv you will use to convert the date to internal format.

After that, my advise for the output format applies.

[aditya]

"SEARCH" the forum.

I know this had been answered before!

[tsnmurthy]

Srini,

You can develop a routine for that and convert in to Oracle date format.

use 'OCONV' & ICONV functions in your routine and the date format choose 'D-YMD'.

correct me if i am wrong..

[sb_akarmarkar]

Srini,
Try following...

OCONV(ICONV(Arg1 + 1900000,"D2/"),"D4/MDY")


Thanks,
Anupam