Varchar to Date format

pxraja · Post by **pxraja** » Sun Sep 21, 2008 11:05 pm

Hi All,

I have the column with datatype Varchar and the values in that are in the format 20080922 I want the format as 22/09/2008 in the target table under date datatype

Inlink.Field1 Outlink.Field1
20080922 22/09/2008

while using the Oconv(Iconv(Inlink.Field1,'DYMD[4,2,2]'),'D DMY[2,2,4]') I am getting the error like datetime field overflow, also I tried with

Oconv(Iconv(Field(Inlink.Field1,"",1,1),'DYMD[4,2,2]'),'D DMY[2,2,4]')

I am getting the same error, can anyone tell me how to overcome this error.

Thanks in Advance

ray.wurlod · Post by **ray.wurlod** » Mon Sep 22, 2008 12:28 am

Use substrings and concatenation - it's far more efficient.

Code: Select all

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

If you must use Oconv() include the delimiter in the format specification.

Code: Select all

Oconv(Iconv(Inlink.Field1,'DYMD[4,2,2]'),'D/DMY[2,2,4]')

pxraja · Post by **pxraja** » Mon Sep 22, 2008 12:48 am

ray.wurlod wrote:Use substrings and concatenation - it's far more efficient.

Code: Select all

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


Thanks for your suggestion


But Ray, I want this to be a date data type will your code changes to date data type..

I may restrict this to month and year also, i.e Outlink.Field1 may changed to display only month and year like

09/2008

so I want to convert the Inlink.Field1 as date format.

your suggestions are welcome

Thanks in Advance

pxraja · Post by **pxraja** » Mon Sep 22, 2008 1:03 am

Also,

Oracle table structure is having DATE datatype for the Outlink.Field1 so I need to convert the incoming record into Date format.

I tried to send incoming record directly without any transformation but its throwing error like DateField overflow error and the job gets aborted.

Any suggestions and ideas are welcome

Thanks in Advance

ray.wurlod · Post by **ray.wurlod** » Mon Sep 22, 2008 1:19 am

There are NO DATA TYPES in server jobs. You can use any operator you like.

pxraja · Post by **pxraja** » Mon Sep 22, 2008 1:46 am

ray.wurlod wrote:There are NO DATA TYPES in server jobs. You can use any operator you like. ...

In oracle table DATE datatype is used for the field Outlink.Field1, job gets aborted when i am using your code(after inserting my Inlink.Field1) its giving DATATIME field overflow Error in parameter 5.

Any suggestions on this

Thanks in advance

ray.wurlod · Post by **ray.wurlod** » Mon Sep 22, 2008 1:51 am

DATATIME I doubt; DATETIME I'd believe. Did you import the Oracle table definition? Did it come in as Date or Timestamp?

pxraja · Post by **pxraja** » Mon Sep 22, 2008 1:59 am

ray.wurlod wrote:DATATIME I doubt; DATETIME I'd believe. Did you import the Oracle table definition? Did it come in as Date or Timestamp? ...

Yeah, its DateTime Field only. Its coming as Timestamp whenever defining the DATE datatype for any Fields. Timestamp(19) is used. Even I tried with Date datatype in Outlink.Field1 records are not viewed.

Oconv(Iconv(Inlink.Field1,'YMD[4,2,2]'),'DMY[2,2,4]') is running but the Outlink.Field1 is empty.

I couldnot able to understand its behaviour.

Have anyone faced this problem, give me your suggestions.

Thanks in advance

ray.wurlod · Post by **ray.wurlod** » Mon Sep 22, 2008 3:42 am

Oracle wants a timestamp. Give it one. That means (almost certainly) year-month-day order in the date portion and "proper" delimiters. In short, what you get when you SELECT this field from Oracle.

Code: Select all

Fmt(InLink.Field1,"L####-##-## 00:00:00")

chulett · Post by **chulett** » Mon Sep 22, 2008 6:55 am

The other important question is - is the stage generating sql for you or are you using custom sql? Generated sql will use a TO_DATE() and expect your data in YYYY-MM-DD HH24:MI:SS format, which is by far the best way to go.

pxraja · Post by **pxraja** » Mon Sep 22, 2008 9:22 pm

chulett wrote:The other important question is - is the stage generating sql for you or are you using custom sql? Generated sql will use a TO_DATE() and expect your data in YYYY-MM-DD HH24:MI:SS format, which ...

Source is from the flat file, the date field is coming as 20080922 and its taken to odbc stage as varchar, but the problem is in ODS, when trying to convert the varchar to date. ODS table structure is having the particular field as date datatype. Record is not inserted into the table and the job gets aborted.

So I would like to convert it to date format in the transformer stage.

Any ideas are welcome

Thanks in advance

pxraja · Post by **pxraja** » Mon Sep 22, 2008 10:27 pm

chulett wrote:The other important question is - is the stage generating sql for you or are you using custom sql? Generated sql will use a TO_DATE() and expect your data in YYYY-MM-DD HH24:MI:SS format, which ...

I am using custom query to select fields, and the date field is simply taken in without any transforms.

Should I workaround the sql query to change to date data type? or is it possible to change in the transformer?

Any suggestions are welcome

Thanks in advance

ray.wurlod · Post by **ray.wurlod** » Tue Sep 23, 2008 12:52 am

It's VarChar according to your original post in this thread. How you get it is irrelevant.

pxraja · Post by **pxraja** » Tue Sep 23, 2008 4:27 am

ray.wurlod wrote:It's VarChar according to your original post in this thread. How you get it is irrelevant. ...

I am getting the VarChar datatype in the source but I meant to say should I look for the sql query to change the format from 20080922 to 2008-09-22 and then pass it to the target table?

Any suggestions?

Thanks in Advance

ray.wurlod · Post by **ray.wurlod** » Tue Sep 23, 2008 4:29 am

If you wish. It's a viable alternative.