Desired date format (YYYY-MM-DD) to be loaded to Oracle DB
Moderators: chulett, rschirm, roy
Re: Desired date format (YYYY-MM-DD) to be loaded to Oracle
If your input data is in YYYY-MM-DD format, then leave it as it is. During the load, if you are using any of the Oracle stages, it will convert it to the desired Oracle format (MM/DD/YYYY). The load stage performs a TO_DATE function on the input date which results in the format as is expected by Oracle.
However, if your intention is to store the dates in Oracle in YYYY-MM-DD, then it is outside the scope of DataStage, and has to be done in Oracle.
However, if your intention is to store the dates in Oracle in YYYY-MM-DD, then it is outside the scope of DataStage, and has to be done in Oracle.
gateleys
![Exclamation :!:](./images/smilies/icon_exclaim.gif)
![Shocked :shock:](./images/smilies/icon_eek.gif)
If you have a successful process, why not stick with it? The 'default date format' being discussed is for external processes and is only used during a load when it has no clue what format your date is coming in as. Build processes that don't rely on any particular date format - specify it in your job. That's why stages generally build their own SQL and leverage the TO_DATE() and TO_CHAR() functions.
ps. There's no such thing as 'storing dates in Oracle in YYYY-MM-DD format'.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
You talkin' to me? ........ You talkin' to me? .....chulett wrote:I'd be really careful about giving advice like that - any idea how many processes that rely on the NLS_DATE format being defaulted that you might break with that change?
![]()
![Cool 8)](./images/smilies/icon_cool.gif)
Ah! Well, I am not the only one here .
![Laughing :lol:](./images/smilies/icon_lol.gif)
[Watched Taxi Driver last night .... again!!]
![Wink :wink:](./images/smilies/icon_wink.gif)
gateleys