Varchar to Date format
Moderators: chulett, rschirm, roy
Varchar to Date format
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Use substrings and concatenation - it's far more efficient.
If you must use Oconv() include the delimiter in the format specification.
Code: Select all
Right(InLink.TheString,2) : "/" : InLink.TheString[5,2] : "/" : Left(InLink.TheString,4)
Code: Select all
Oconv(Iconv(Inlink.Field1,'DYMD[4,2,2]'),'D/DMY[2,2,4]')
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.ray.wurlod wrote:There are NO DATA TYPES in server jobs. You can use any operator you like. ...
Any suggestions on this
Thanks in advance
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.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? ...
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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")
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.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 ...
So I would like to convert it to date format in the transformer stage.
Any ideas are welcome
Thanks in advance
I am using custom query to select fields, and the date field is simply taken in without any transforms.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 ...
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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?ray.wurlod wrote:It's VarChar according to your original post in this thread. How you get it is irrelevant. ...
Any suggestions?
Thanks in Advance
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: