Page 1 of 3
Varchar to Date format
Posted: Sun Sep 21, 2008 11:05 pm
by pxraja
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
Posted: Mon Sep 22, 2008 12:28 am
by ray.wurlod
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]')
Posted: Mon Sep 22, 2008 12:48 am
by pxraja
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
Posted: Mon Sep 22, 2008 1:03 am
by pxraja
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
Posted: Mon Sep 22, 2008 1:19 am
by ray.wurlod
There are NO DATA TYPES in server jobs. You can use any operator you like.
Posted: Mon Sep 22, 2008 1:46 am
by pxraja
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
Posted: Mon Sep 22, 2008 1:51 am
by ray.wurlod
DATATIME I doubt; DATETIME I'd believe. Did you import the Oracle table definition? Did it come in as Date or Timestamp?
Posted: Mon Sep 22, 2008 1:59 am
by pxraja
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
Posted: Mon Sep 22, 2008 3:42 am
by ray.wurlod
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")
Posted: Mon Sep 22, 2008 6:55 am
by chulett
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.
Posted: Mon Sep 22, 2008 9:22 pm
by pxraja
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
Posted: Mon Sep 22, 2008 10:27 pm
by pxraja
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
Posted: Tue Sep 23, 2008 12:52 am
by ray.wurlod
It's VarChar according to your original post in this thread. How you get it is irrelevant.
Posted: Tue Sep 23, 2008 4:27 am
by pxraja
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
Posted: Tue Sep 23, 2008 4:29 am
by ray.wurlod
If you wish. It's a viable alternative.