Different date formats

dataservices job design
1

Hi,
My incomming soure column has 2 different data formats, 20/05/1996 dd/mm/yyyy and 2004.05.25 00:00:00 YYYY.MM.DD HH:MI:SS

My function
decode(is_valid_date(input,‘dd/mm/yyy’ )=1
to_date(input, ‘YYYY.MM.DD HH:MI:SS’),‘dd/mm/yyyy hh:mi:ss’),
to_date(input, ‘mm/dd/yyyy’),‘dd/mm/yyyy hh:mi:ss’))

the function which i used is not working can i know the correct date function,i would like to convert these both dates into ‘DD/MM/YYY HH:I:SS’ format.

Thanx

kiran344 (BOB member since 2010-04-12)

2

I don’t know if what you posted was a typo on your part or not. Also, your true/false logic was backwards. Once the strings are converted to a date data type there is no need to convert them again unless the target column is also a string. Try this instead:

decode(is_valid_date(input,'dd/mm/yyyy' )=1 
to_date(input, 'dd/mm/yyyy'),
to_date(input, 'YYYY.MM.DD HH:MI:SS'))

Note that in the first date format you used yyy instead of yyyy.

If that doesn’t work then consider changing how to determine which format applies. Look to see if the fourth column contains a period or a number. If it is a period then convert using YYYY.MM.DD HH:MI:SS otherwise use dd/mm/yyyy.

eganjp :us: (BOB member since 2007-09-12)

3

Hi thanx for ur reply.

In my target table all the date formats are stored in dd/mm/yy hh:mi:ss format, to maintain consistency throughout the table, i am trying to convert two different date formats in a single column into above format.

i am using varchar datatype to that column as i am trying to convert in specific format.

function is still not working can you suggest any changes!

Thanx

kiran344 (BOB member since 2010-04-12)

4

Try HH24

If it is still not working, how? What is your output? Are you sure there isn’t a third format?

PS you don’t convert in to a date format, a date is a date, you are trying to tell DS how it should read a date string, how the date displays is down to how you are viewing the date (clients will format according to specification)

Kryt0n (BOB member since 2010-10-14)

5

Like kryt0n said, you need to provide more information. Try doing some debugging on your own. Look at the incoming data and try the decode directly against the database using a tool such as SQL Developer, Toad or even SQL Plus. I suspect you’ll find that not all your data matches your expectations.

eganjp :us: (BOB member since 2007-09-12)