Varchar to Date format

ray.wurlod · Post by **ray.wurlod** » Sat Sep 27, 2008 3:10 am

Substrings and concatenation will be the most efficient, and remember that there are no data types within DataStage server jobs.

Code: Select all

Right(InLink.TheString,2) : "/" : InLink.TheString[5,2] : "/" : Left(InLink.TheString,4)

Another nice thing about this solution is that it works both for YYYYMMDD date strings and for YYYYDDMM date strings.

pxraja · Post by **pxraja** » Wed Oct 01, 2008 6:48 am

ray.wurlod wrote:Substrings and concatenation will be the most efficient, and remember that there are no data types within DataStage server jobs.

Code: Select all

Right(InLink.TheString,2) : "/" : InLi ...[/quote]

Hi Ray,

I am using custom query in the ODBC STAGE, in that I am using 

TO_DATE(Field1,'YYYYMMDD') getting as Inlink.Field1 and in the transformer I am using
Inlink.Field1[8,2]:"/":Inlink3.Field[5,2]:"/":Left(Inlink.Field,4) and getting to Outlink.Field1 whose datatype is DATE in Oracle database.

as you are saying I tried for running 1000 rows, It has passed from Inlink.Field1 upto transformer but the problem is from transformer to target ODBC no rows were populated.

while viewing in the ODBC Stage the data appears as 
2008-02-22 00:00:00

whats wrong with the query? any suggestions plz?

Thanks in advance

chulett · Post by **chulett** » Wed Oct 01, 2008 7:14 am

chulett wrote:When you use TO_DATE() all that matters is that your mask/picture matches the data.

Yours still doesn't.

deva · Post by **deva** » Wed Oct 01, 2008 8:06 am

Hi Can you try with the follwoing formate

Oconv(Iconv(DSLink3.FieldName,"D-DMY[2,3,2]"),"D/DMY[2,2,2]") Or

Oconv(Iconv(DSLink3.HIREDATE,"D DMY[2,3,2]"),"D/DMY[2,2,2]")

deva · Post by **deva** » Wed Oct 01, 2008 8:09 am

Hi Can you try with the follwoing formate

Oconv(Iconv(DSLink3.FieldName,"D-DMY[2,3,2]"),"D/DMY[2,2,2]") Or

Oconv(Iconv(DSLink3.HIREDATE,"D DMY[2,3,2]"),"D/DMY[2,2,2]")

chulett · Post by **chulett** » Wed Oct 01, 2008 8:19 am

Not going to help.

pxraja · Post by **pxraja** » Sat Oct 04, 2008 1:01 am

deva wrote:Hi Can you try with the follwoing formate

Oconv(Iconv(DSLink3.FieldName,"D-DMY[2,3,2]"),"D/DMY[2,2,2]") Or

Oconv(Iconv(DSLink3.HIREDATE,"D DMY[2,3,2]"),"D/DMY[2,2,2]")

i found my nls_session_parameters having date format DD-MON-RR, and tried for

my custom query having TO_DATE(Inlink.Field1,'YYYYMMDD')

Oconv(Iconv(Substrings(Inlink.Field1,1,10),'D-YMD[4,2,2]'),'D-DMY[2,3,2]')

this code is working fine with tabel having Outlink.Field1 with varchar datatype, tested for 1000 rows Outlink.Field1 having 1000 rows with desired output

when I changed to table having Outlink.Field1 with DATE datatype its not inserting records into the table,i.e, Outlink.Field1 is NULL with 1000 rows.

I couldnot get into this behaviour, any suggestions please

thanks in advance

ray.wurlod · Post by **ray.wurlod** » Sat Oct 04, 2008 1:32 am

If you want alphbetic month characters you have to specify the same. One way to do this is:

Code: Select all

Oconv(Iconv(Left(Inlink.Field1,10),'DYMD[4,2,2]'),'D-DMY[2,A3,2]')

Another way is

Code: Select all

Oconv(Iconv(Left(Inlink.Field1,10),'DYMD[4,2,2]'),'D2-DMBY')

Don't use Substrings() - it's less efficient because it includes code to handle multi-valued data. Prefer regular substring (the square bracket notation) or Left() or Right() function where relevant.

pxraja · Post by **pxraja** » Sat Oct 04, 2008 2:47 am

ray.wurlod wrote:If you want alphbetic month characters you have to specify the same. One way to do this is:

Code: Select all

Oconv(Iconv(Left(Inlink.Field1,10),'DYMD[4,2,2]'),'D-DMY[2,A3,2] ...[/quote]

I tried your code, but its not populating records into Outlink.Field1.

my nls_date_format is like DD-MON-RR but when viewing in TOAD editor it display's like 22/09/2008 why is it so? anyrelation with the record not entering the table?

Any suggestions for understanding this ?

Thanks in Advance

ray.wurlod · Post by **ray.wurlod** » Sat Oct 04, 2008 3:30 am

Get your head around this idea.

A date is a date is a date.

Inside Oracle (or any other database, pretty much) it is stored as a binary value.

Different tools have different requirements and standards as to how the date should be represented for human consumption. Usually these are configurable (for example in Oracle via the TO_DATE and TO_CHAR functions with different date format pictures). Where they are not configurable (for example ODBC) then you must play the game by those rules. Dates supplied to ODBC must be in YYYY-MM-DD format. No exceptions.

pxraja · Post by **pxraja** » Sat Oct 04, 2008 3:51 am

ray.wurlod wrote:Get your head around this idea.

A date is a date is a date.

Inside Oracle (or any other database, pretty much) it is stored as a binary value.

Different tools have different requirements and ...

my question is why its not populating the records into the target table in oracle?

No lights blowing on my head.

any suggestions please

thanks in advance

ray.wurlod · Post by **ray.wurlod** » Sat Oct 04, 2008 4:33 am

Suggestion: buy a premium membership so you can read my suggestions. The answer is, I believe, in those parts of the answer that you can not see.

chulett · Post by **chulett** » Sat Oct 04, 2008 8:02 am

Never mind the fact that I've already explained what you need to do to make this work. Can't believe we're still going round and round and up to 3 pages now.