Hi,
Any idea how do I convert yyyyy-mm-dd to DD/MM/YYYY in server jobs.
Source column is Timestamp and output column is DATE.
Any thoughts.
Thanks,
yyyyy-mm-dd to DD/MM/YYYY
Moderators: chulett, rschirm, roy
See this post for a discussion of dates and their lack of formats. Yes, it's about Parallel jobs but a DATE is a Date is a date.
Server jobs are very forgiving... what happens when you just move one to the other?
Server jobs are very forgiving... what happens when you just move one to the other?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Does your source really have five digit year? (The good news is that server jobs support this, but can't reliably convert a five digit year into a four digit format.)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Hi,
My source is 2015-01-26 00:00:00 (Timestamp in hash file)
I need to load to Oracle database datatype is DATE.
SELECT sysdate from DUAL;
06-FEB-15
I have tried with few options
OCONV(Iconv(Left(col1,10),'D-YMD'),'D4-YMD[4,2,2]')
Everytime is get this error
Attempt to convert String value "2015-01-26" to Date type unsuccessful
any ideas
My source is 2015-01-26 00:00:00 (Timestamp in hash file)
I need to load to Oracle database datatype is DATE.
SELECT sysdate from DUAL;
06-FEB-15
I have tried with few options
OCONV(Iconv(Left(col1,10),'D-YMD'),'D4-YMD[4,2,2]')
Everytime is get this error
Attempt to convert String value "2015-01-26" to Date type unsuccessful
any ideas
First question, did you try simply moving one to the other without any conversion? I'd also like to point out that this:
Another key point is the fact that an Oracle DATE field is in fact a timestamp so you should always define it as such in your jobs. Even better, let the target stage automatically generate the DML (used to be called Column Generated from what I recall) so you can see what the proper way to handle data of that nature is - the TO_DATE() function with a full timestamp format mask that it will add.
Then your value will move seamlessly from one end to the other without any shenanigans required.
Really means nothing as whatever tool you are using could have decided on the format to show you - the external format to show you. And if you're using it to determine the 'default date format' that's not the best way nor is it something you should ever be relying on. IMHO and in the face of years of experience.srini.dw wrote:SELECT sysdate from DUAL;
06-FEB-15
Another key point is the fact that an Oracle DATE field is in fact a timestamp so you should always define it as such in your jobs. Even better, let the target stage automatically generate the DML (used to be called Column Generated from what I recall) so you can see what the proper way to handle data of that nature is - the TO_DATE() function with a full timestamp format mask that it will add.
Then your value will move seamlessly from one end to the other without any shenanigans required.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Correct!chulett wrote:Another key point is the fact that an Oracle DATE field is in fact a timestamp ...
Do you need to retract your earlier statement? "a DATE is a Date is a date"
Ha Ha Ha! When I first read that, I thought to myself, "yeah, except in Oracle..."
Choose a job you love, and you will never have to work a day in your life. - Confucius