DSXchange

Posted: **Mon Sep 27, 2010 11:27 am**

how to conevrt 020802 usign DS to orcale date?

Posted: **Mon Sep 27, 2010 11:46 am**

Like normal - an Iconv/OConv pair (or substring / concatenation) to get it into a standard format and then a TO_DATE() with a matching format mask in your target SQL.

Posted: **Mon Sep 27, 2010 12:33 pm**

chulett wrote:Like normal - an Iconv/OConv pair (or substring / concatenation) to get it into a standard format and then a TO_DATE() with a matching format mask in your target SQL. ...

I dont see nay functions To_DATE in datastage

Posted: **Mon Sep 27, 2010 12:39 pm**

To_date function is available in Sql and not in DataStage.

Posted: **Mon Sep 27, 2010 12:59 pm**

chulett wrote:... and then a TO_DATE() with a matching format mask in your target SQL. ...

...in your target SQL is what Craig meant.

Posted: **Mon Sep 27, 2010 1:57 pm**

vivekgadwal wrote:
chulett wrote:... and then a TO_DATE() with a matching format mask in your target SQL. ...
...in your target SQL is what Craig meant.

I got where is it but How do I mask it?

Posted: **Mon Sep 27, 2010 2:03 pm**

Write a User-defined SQL query to do the Date masking.

Posted: **Mon Sep 27, 2010 2:11 pm**

Or let the stage generate the SQL. If your data types are correct in the job, it will automatically use a TO_DATE() in the generated sql.

Posted: **Mon Sep 27, 2010 4:53 pm**

What does 020802 actually mean? Is it YMD, DMY, MDY format? Is it a Julian date of some kind?

Posted: **Mon Sep 27, 2010 5:24 pm**

ray.wurlod wrote:What does 020802 actually mean? Is it YMD, DMY, MDY format? Is it a Julian date of some kind? ...

its in MMDDYY format. SQL it generates asTO_DATE(:5, 'DD-MON-YY'), let me know how to make it work..I dont get anything to the oracle db.

Thanks

Posted: **Mon Sep 27, 2010 5:26 pm**

Convert it into the format it is expecting.

Posted: **Mon Sep 27, 2010 5:48 pm**

India2000 wrote:
ray.wurlod wrote:What does 020802 actually mean? Is it YMD, DMY, MDY format? Is it a Julian date of some kind? ...
its in MMDDYY format. SQL it generates asTO_DATE(:5, 'DD-MON-YY'), let me know how to make it work..I dont get anything to the oracle db.

Thanks

oconv(iconv( 020802,'d6']'),'d-dmy[2,a3,2]')

what's wrong in this conversion with respect to the SQL given above?

Posted: **Mon Sep 27, 2010 8:24 pm**

"d6" is not a valid argument for Iconv(). You need something like "DMDY". All date arguments for Iconv() and Oconv() must be in upper case.

Posted: **Mon Sep 27, 2010 8:26 pm**

Perhaps IConv(YourDate,'DMDY[2,2,2]') for the internal conversion part?

Posted: **Mon Sep 27, 2010 8:32 pm**

ray.wurlod wrote:"d6" is not a valid argument for Iconv(). You need something like "DMDY". All date arguments for Iconv() and Oconv() must be in upper case. ...

I changed it to Oconv( Iconv( Date,"D-MDY[2,2,4]"),"D-DMY[2,A3,2]") as per the sql generated to_date(:5,'DD-MON-YY') but still it doesnt work..where am I going wrong.. After Oconv i get the same value as it only with Iconv. ie in integer format