I have a job in the version 4.2 that records the date of the system at format YYYY-MM-DD in the bank Oracle. I used the function Oconv to define the format.
I migrated this job for the version 7.5 and when executing, the date was not recorded. I altered the function Oconv to record at format YYYY-MMM-DD (2006-JUL-01) and it recorded.
The bank Oracle is the same, only the servers DataStage are different. Is there a variable in DataStage that configures the format of the date? For me not to have to alter all the jobs.
Does anybody can me to help?
Variable DataStage for format Date
Moderators: chulett, rschirm, roy
Hi,
(Please post Server jobs in the Sever Forum)
Ususally the way you see the dates is controlled by the client and the environment configuration not by DS (unless as you mentioned you specificly define it)
Try to see if any Environment variables are set in either server and consult your DBAs.
Besides the above oracle dates are stored in a date format which you may format as will by the TO_CHAR(<date>,<format>) function.
IHTH,
(Please post Server jobs in the Sever Forum)
Ususally the way you see the dates is controlled by the client and the environment configuration not by DS (unless as you mentioned you specificly define it)
Try to see if any Environment variables are set in either server and consult your DBAs.
Besides the above oracle dates are stored in a date format which you may format as will by the TO_CHAR(<date>,<format>) function.
IHTH,
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Looks to me like your Oracle dates were being selected in YYYY-MM-DD format under 4.2 but after upgrading to 7.5 they are in YYYY-MMM-DD format. I'd be curious what stage you are using (OCI?) and what datatype you are using in your source stage - Date? Varchar?
As Roy notes, best practice is to not assume dates will be selected in any particular format but to force a conversion to a specific format. The OCI stage does this 'automatically' when you pick a Date or Timestamp datatype and allow the stage to generate the sql, the stage will add a standard TO_CHAR() to the source query - and a TO_DATE() in the target DML. I stick with 'Timestamp' when dealing with Oracle dates.
Now, if is this something your jobs are currently doing, leveraging the TO_CHAR sql generated by the stage, perhaps that behaviour has changed from 4.2 to 7.5? That would be... unfortunate... if true.
As Roy notes, best practice is to not assume dates will be selected in any particular format but to force a conversion to a specific format. The OCI stage does this 'automatically' when you pick a Date or Timestamp datatype and allow the stage to generate the sql, the stage will add a standard TO_CHAR() to the source query - and a TO_DATE() in the target DML. I stick with 'Timestamp' when dealing with Oracle dates.
Now, if is this something your jobs are currently doing, leveraging the TO_CHAR sql generated by the stage, perhaps that behaviour has changed from 4.2 to 7.5? That would be... unfortunate... if true.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers