String to Date Format

kumar66 · Post by **kumar66** » Wed Apr 09, 2008 4:04 pm

Hi All,

I need to change the string to date. My source has the value 20070125 and I need to chnage it date. My tagret is Teradata.

I used

" Oconv(Iconv(Input._DT, "DYMD"), "D-YMD[4,2,2])" .

But the row is geting rejected.

Please Advise.

Thanks & Regards,
Kumar66

ray.wurlod · Post by **ray.wurlod** » Wed Apr 09, 2008 4:39 pm

What format does Teradata require?
Your Iconv() function is good.
Your Oconv() function is good only if Teradata requires YYYY-MM-DD format.
What error message is logged?

kumar66 · Post by **kumar66** » Wed Apr 09, 2008 5:07 pm

Hi Ray,

I am getting following warning:

Value treated as NULL
Attempt to convert String value "2007-01-25" to Date type unsuccessful.

My target format is "YYYY-MM-DD".

Thanks & Regards,
Kumar66

chulett · Post by **chulett** » Wed Apr 09, 2008 6:06 pm

Apparently, that must not be your target date format or all would be working. Can you check with your TeraData DBA how it should be formatted?

ray.wurlod · Post by **ray.wurlod** » Wed Apr 09, 2008 6:44 pm

Please post the full warning. We need to know where it is being generated.

If it is being generated in the Transformer stage (no Teradata stamps in the message) then the derivation is awry (which I doubt).

Break up the expression into two stage variables, one with the Iconv() and one with the Oconv() function. Use stage tracing or the Debugger to determine whether it is the Iconv() or Oconv() function that is failing, or neither.

If the message is stamped as having been generated by Teradata (or by an ODBC driver), then verify that the expected date format is as you posted. Don't assume, check.

Saad · Post by **Saad** » Thu Apr 10, 2008 9:39 am

Kumar,

Try inserting the record simply using Iconv(Input._DT, "DMYD"), actually this generates an internal value which Teradata recognizes and inserts properly. We had been doing this in one of our projects.

Regards,

Saad

kumar66 · Post by **kumar66** » Thu Apr 10, 2008 2:25 pm

Hi Ray,

I have posted the full warning :

Stg..TX_MTRF: At row 1, link "out_TRF", while processing column "EFF_DT"
Value treated as NULL
Attempt to convert String value "2007-01-25" to Date type unsuccessful

Stg..TX_MTRF: Column EFF_DT cannot be null.
Stg..TX_MTRF: EFF_DT = NULL FRCH_ID = 1249 RTL_CTRY_CD = PH FRCH_LOC_ID = ABCD TRF_QTY = 5

Thanks & Regards,
Kumar66

kumar66 · Post by **kumar66** » Thu Apr 10, 2008 2:27 pm

Hi Saad,

Thanks for your suggestion. I tried the way you suggested me . But the rows are getting rejected.

Thanlks & Regards,
Kumar66

DSguru2B · Post by **DSguru2B** » Thu Apr 10, 2008 3:04 pm

Try once again Saad's suggestion with mask "DYMD[4,2,2]"

kumar66 · Post by **kumar66** » Thu Apr 10, 2008 3:43 pm

Hi DSguru2B,

I tried that , it working but when I open the Teradata API stage and view the data it has internal value " 14270" instead "2007-01-25".

Thanks & Regards,
Kumar66

DSguru2B · Post by **DSguru2B** » Fri Apr 11, 2008 8:00 am

Extend the column, you should see the proper format also. Anywho, check for the data using your favourite sql tool, not DataStage. This way you will be able to tell for sure.

kumar66 · Post by **kumar66** » Fri Apr 11, 2008 10:22 am

Hi DSguru2B,

Thanks for your reply. Yes .I am able to view the correct date format in Teradata SQL Assistant.

But the Teradata stage shows the internal format. Just curious , why it gives the internal format.

Thanks & Regards,
Kumar66