Unable to convert integer date (YYYYMMDD) to date
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 21
- Joined: Tue Jul 18, 2006 2:38 pm
Unable to convert integer date (YYYYMMDD) to date
Hi,
I am trying to convert an integer date 20060706 to date 2006-07-06.
Used Modify stage: - m_OHEDAT:date = date_from_string[%yyyy-%mm-%dd](OHEDAT) -- Output is NULL.
Message:APT_CombinedOperatorController,1: Numeric string expected . Use default value. Converted the integer to string still got the above message.
Transformer:StringToDate(lkup_cust_out.OHEDAT,"%yyyy-%mm-%dd")-- Output is *********.
I also has a Julian date YYYYDDD (2006207) need to be converted to regular date (YYYYMMDD)
Can some one help me.
Thanks
Omkar
I am trying to convert an integer date 20060706 to date 2006-07-06.
Used Modify stage: - m_OHEDAT:date = date_from_string[%yyyy-%mm-%dd](OHEDAT) -- Output is NULL.
Message:APT_CombinedOperatorController,1: Numeric string expected . Use default value. Converted the integer to string still got the above message.
Transformer:StringToDate(lkup_cust_out.OHEDAT,"%yyyy-%mm-%dd")-- Output is *********.
I also has a Julian date YYYYDDD (2006207) need to be converted to regular date (YYYYMMDD)
Can some one help me.
Thanks
Omkar
Omkar
Re: Unable to convert integer date (YYYYMMDD) to date
Can't help with the other, but just wanted to be be picky and say that what you've got is an Ordinal Date, not a Julian Date.nekkalapudi wrote:I also has a Julian date YYYYDDD (2006207) need to be converted to regular date (YYYYMMDD)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Charter Member
- Posts: 64
- Joined: Sat Sep 17, 2005 10:42 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
All you need is date_from_string[%yyyy%ddd](InputDate) in a Modify stage, or StringToDate(InputDate, "%yyyy%ddd") in a Transformer stage.
The result is a date. Its internal format is immaterial. It can be used to populate a field whose data type is date.
If you need to convert it back to a string, then use DateToString() in a Transformer stage. Nested functions are not supported in a Modify stage.
The result is a date. Its internal format is immaterial. It can be used to populate a field whose data type is date.
If you need to convert it back to a string, then use DateToString() in a Transformer stage. Nested functions are not supported in a Modify stage.
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.
-
- Participant
- Posts: 34
- Joined: Thu Jul 29, 2004 1:03 pm
Re: Unable to convert integer date (YYYYMMDD) to date
Transformer:StringToDate(lkup_cust_out.OHEDAT,"%yyyy-%mm-%dd")-- Output is *********.
Since you said your date is in the format YYYYMMDD you will need to specify StringToDate(lkup_cust_out.OHEDAT,"%yyyy%mm%dd") in you transformer
I also has a Julian date YYYYDDD (2006207) need to be converted to regular date (YYYYMMDD)
For this, use the function DateFromDaysSince(%number%,[%"yyyy-mm-dd"%]). For the example you have given it will be DateFromDaysSince(207, "2005-12-31"), will give you 2006-07-26.
HTH
Thanks,
Shekar
Since you said your date is in the format YYYYMMDD you will need to specify StringToDate(lkup_cust_out.OHEDAT,"%yyyy%mm%dd") in you transformer
I also has a Julian date YYYYDDD (2006207) need to be converted to regular date (YYYYMMDD)
For this, use the function DateFromDaysSince(%number%,[%"yyyy-mm-dd"%]). For the example you have given it will be DateFromDaysSince(207, "2005-12-31"), will give you 2006-07-26.
HTH
Thanks,
Shekar
-
- Participant
- Posts: 21
- Joined: Tue Jul 18, 2006 2:38 pm
Re: Unable to convert integer date (YYYYMMDD) to date
All,
Changed format. Every thing is working.
Thanks for the information.
Omkar
Changed format. Every thing is working.
Thanks for the information.
Omkar
nekkalapudi wrote:Hi,
I am trying to convert an integer date 20060706 to date 2006-07-06.
Used Modify stage: - m_OHEDAT:date = date_from_string[%yyyy-%mm-%dd](OHEDAT) -- Output is NULL.
Message:APT_CombinedOperatorController,1: Numeric string expected . Use default value. Converted the integer to string still got the above message.
Transformer:StringToDate(lkup_cust_out.OHEDAT,"%yyyy-%mm-%dd")-- Output is *********.
I also has a Julian date YYYYDDD (2006207) need to be converted to regular date (YYYYMMDD)
Can some one help me.
Thanks
Omkar
Omkar
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
If you specify "-" in the format string, then the data must also have this delimiter character. A format string of "%yyyy%mm%dd" would be more appropriate (in both Modify and Transformer stage types) where your souce date is of the form YYYYMMDD. No delimiters, see?
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.
I am also having similar problem and haven't figured out yet even though I searched the forum and read several thread. I would appreciate if someone help regarding date conversion issue.
here is a detailed description
my source is a flat file which is delimited text produced by exporting a table from Ms access.
I can see the date in the flat file as 7/25/2006 15:04:00
in the job, my input colum definition is varchar with lenght 38, or I can also read as unknown field type of length 38. target column definition is timestamp with length 38.
in a transformer I need to convert it to oracle date ( which is date and time ) but the job is aborting because of this date derivation column I have in the transformer. I tried a lot of different function in this derivation but couldnot make it work. I want to put input date and time into oracle with both date and time, i.e., timestamp ex ( 8/10/2006 11:30:37 AM )
the log says
Job name:State_Agency_Ind_Updated_Date3
Event #:256
Timestamp:8/10/2006 2:58:51 PM
Event type:Fatal
User:dsadmin
Message:
S_A_IND_UPDATED_DATE,0: Failure during execution of operator logic.
here is a detailed description
my source is a flat file which is delimited text produced by exporting a table from Ms access.
I can see the date in the flat file as 7/25/2006 15:04:00
in the job, my input colum definition is varchar with lenght 38, or I can also read as unknown field type of length 38. target column definition is timestamp with length 38.
in a transformer I need to convert it to oracle date ( which is date and time ) but the job is aborting because of this date derivation column I have in the transformer. I tried a lot of different function in this derivation but couldnot make it work. I want to put input date and time into oracle with both date and time, i.e., timestamp ex ( 8/10/2006 11:30:37 AM )
the log says
Job name:State_Agency_Ind_Updated_Date3
Event #:256
Timestamp:8/10/2006 2:58:51 PM
Event type:Fatal
User:dsadmin
Message:
S_A_IND_UPDATED_DATE,0: Failure during execution of operator logic.
Thanks,
Chad
__________________________________________________________________
"There are three kinds of people in this world; Ones who know how to count and the others who don't know how to count !"
Chad
__________________________________________________________________
"There are three kinds of people in this world; Ones who know how to count and the others who don't know how to count !"
what is the type conversion you are using in the Derivation of the Date Column? Based upon your input date this is how you should define your derivation
based on the datatype in your output.
Code: Select all
StringToTimestamp(%string%,[%"%mm/%dd/%yyyy %hh:%nn:%ss"%])
Or
StringToDate(%datestring%,[%"%mm/%dd/%yyyy"%])
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
Hi Kris Thank you very much for the advise.
I am using the first one you suggested, however it doesnot work ( gives the run time error I posted in my previous post)
the second one you suggested is not what we want because we need time as well as date.
In the left side of transformer, I defined that column as varchar 38 and in the right side I defined as timestamp 38
there is no problem with reading the flat file,
my target is oracle table oracle enterprise stage.
source is flat file
environment is IBM AIX 5.3
DS EE 7.5.1A
I am using the first one you suggested, however it doesnot work ( gives the run time error I posted in my previous post)
the second one you suggested is not what we want because we need time as well as date.
In the left side of transformer, I defined that column as varchar 38 and in the right side I defined as timestamp 38
there is no problem with reading the flat file,
my target is oracle table oracle enterprise stage.
source is flat file
environment is IBM AIX 5.3
DS EE 7.5.1A
Thanks,
Chad
__________________________________________________________________
"There are three kinds of people in this world; Ones who know how to count and the others who don't know how to count !"
Chad
__________________________________________________________________
"There are three kinds of people in this world; Ones who know how to count and the others who don't know how to count !"
I guess this is what is happening. Oracle accepts date format in YYYY-MM-DD HH24:MI:SS.
So, I guess you need to prepare your input data in that format before you load it into Oracle. Convert the Varchar into Timestamp in a Stage Variable and then parse the Timestamp into the required format using DateTime functions or substring functions, whichever works for you. Then try inserting it into Oracle and let us know if the problem still persists.
Does the Job give out any ORA errors in particular. That might help too.
IHTH
So, I guess you need to prepare your input data in that format before you load it into Oracle. Convert the Varchar into Timestamp in a Stage Variable and then parse the Timestamp into the required format using DateTime functions or substring functions, whichever works for you. Then try inserting it into Oracle and let us know if the problem still persists.
Does the Job give out any ORA errors in particular. That might help too.
IHTH
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
no the job doesnot give any ora error, just Datastage error, ok I will try your suggestion not exactly sure how to do thoughkris007 wrote:I guess this is what is happening. Oracle accepts date format in YYYY-MM-DD HH24:MI:SS.
So, I guess you need to prepare your input data in that format before you load it into Oracle. Convert the Varchar into Timestamp in a Stage Variable and then parse the Timestamp into the required format using DateTime functions or substring functions, whichever works for you. Then try inserting it into Oracle and let us know if the problem still persists.
Does the Job give out any ORA errors in particular. That might help too.
IHTH
but thank you very much again
Thanks,
Chad
__________________________________________________________________
"There are three kinds of people in this world; Ones who know how to count and the others who don't know how to count !"
Chad
__________________________________________________________________
"There are three kinds of people in this world; Ones who know how to count and the others who don't know how to count !"