Date Conversion

[balu1625]

The source is AS400 and the date column has the datatype as decimal and its coming as either 0's or 950401(YYMMDD). I tried using Iconv Oconv and also DateTimeStampToODBC routine still im getting the error Attempt to convert String value "0--" to Date type unsuccessful.

I appreciate any inputs to address the issue

[anbu]

Check if the input has only 0's then assign default date else use your function to change the date format

[kumar_s]

As specified, 0 is not a valid value for given Pattern.
Default the 0 to a valid date value such as 510101.

[balu1625]

I have written a function like this

If Input.a = 0 Then @NULL Else ( If (Input.a [1, 1]<='2') Then '20' :
Input.a [2, 2] : '-' : Input.a [4, 2]
: '-' : Input.a [6, 2] Else '19' : Input.a [1, 2] : '-' : Input.a [3, 2] : '-' : Input.a [5, 2]) but still Im getting the error "Attempt to convert String value "1995-05-01" to Date type unsuccessful"

Is there an issue with my syntax.

If the source column comes with 0 I want to populate null and incase if it comes with 960601 then i want to populate as 1996-06-01.

[anbu]

What is your target database?

[balu1625]

Oracle is the target database.

Can I do this conversion using Iconv,Oconv. If so can you plz let me know the syntax.

[arunkumarmm]

Here you go

oconv(iconv(vDate,'d4'),'d-ymd[4,2,2]')

vDate is your incoming date

[anbu]

What you are doing is correct. You need to add to_date function in the target database sql.

Code: Select all

TO_DATE(dte, 'YYYY-MM-DD')

[balu1625]

Thank you so much for your time and help.
I have just tried using
If In_RCS_PIRPRDDBL_OPMPREP.MPPHDT = '0' Then @NULL Else oconv(iconv(In_RCS_PIRPRDDBL_OPMPREP.MPPHDT,'d4'),'d-ymd[4,2,2]')
and the to_date is already used in the target table still Im getting the error

"Output", while processing column "TRANS_DT"
Value treated as NULL
Attempt to convert String value "1995-05-01" to Date type unsuccessful

ORA-01841: (full) year must be between -4713 and +9999, and not be 0

[arunkumarmm]

What if the date format you have in your TO_DATE?

It should be 'YYYY-MM-DD'

[arunkumarmm]

Also no need to check for the 0. iconv will return a NULL if it is not a valid date.

[balu1625]

Thanks for your reply arun. I just saw the format in the output oracle stage. The format is TO_DATE(:7, 'DD-MON-YY'). This is generated sql and I want to change as user defined sql but the tab is grayed out. How can I do that. Also I was going through the server job guide and if the Oconv or Iconv functions sees 0 is it treated as null or anything else?

[balu1625]

Thanks for your reply arun. I just saw the format in the output oracle stage. The format is TO_DATE(:7, 'DD-MON-YY'). This is generated sql and I want to change as user defined sql but the tab is grayed out. How can I do that. Also I was going through the server job guide and if the Oconv or Iconv functions sees 0 is it treated as null or anything else?

[arunkumarmm]

You should be able to change it to Custom SQL, I'm not sure why it is not enabled for you.

And for Iconv((),'d4') will convert any of the following dates to internal format, if it is a value other than a valid date, it will convert it to NULL.

July 4, 2002 July 4 02
4 July 2002 4 July 02
4 JUL 02 4 JUL 2002
04JUL02 04JUL2002
04-JUL-02 04-JUL-2002
07/04/2002 07/04/02
020704 2002/07/04
2002-07-04 20020704

etc.,