yyyyy-mm-dd to DD/MM/YYYY

[srini.dw]

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,

[chulett]

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?

[srini.dw]

Thanks for reply, let me check and get back.

[ray.wurlod]

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.)

[chulett]

I... assumed that was a typo.

[srini.dw]

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

[chulett]

First question, did you try simply moving one to the other without any conversion? I'd also like to point out that this:

SELECT sysdate from DUAL;
06-FEB-15

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.

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.

[qt_ky]

Another key point is the fact that an Oracle DATE field is in fact a timestamp ...

Correct!

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..."

[chulett]

And you know I meant internally. So happy I could provide sig fodder as well.