conversion

India2000 · Post by **India2000** » Mon Sep 27, 2010 11:27 am

how to conevrt 020802 usign DS to orcale date?

chulett · Post by **chulett** » 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.

India2000 · Post by **India2000** » 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

anbu · Post by **anbu** » Mon Sep 27, 2010 12:39 pm

To_date function is available in Sql and not in DataStage.

vivekgadwal · Post by **vivekgadwal** » 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.

India2000 · Post by **India2000** » 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?

vivekgadwal · Post by **vivekgadwal** » Mon Sep 27, 2010 2:03 pm

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

chulett · Post by **chulett** » 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.

ray.wurlod · Post by **ray.wurlod** » 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?

India2000 · Post by **India2000** » 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

chulett · Post by **chulett** » Mon Sep 27, 2010 5:26 pm

Convert it into the format it is expecting.

India2000 · Post by **India2000** » 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?

ray.wurlod · Post by **ray.wurlod** » 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.

chulett · Post by **chulett** » Mon Sep 27, 2010 8:26 pm

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

India2000 · Post by **India2000** » 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