Unable to convert integer date (YYYYMMDD) to date

[nekkalapudi]

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

[chulett]

I also has a Julian date YYYYDDD (2006207) need to be converted to regular date (YYYYMMDD)

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.

[panchusrao2656]

Transformer:StringToDate(lkup_cust_out.OHEDAT,"%yyyy-%mm-%dd")-- Output is *********.



In The Transformer, the following works.

StringToDate(lkup_cust_out.OHEDAT,"%yyyy%mm%dd") unless the supplied date is invalid like 20060500 Or 20060005

[ray.wurlod]

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.

[dsdesigner]

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

[nekkalapudi]

All,

Changed format. Every thing is working.
Thanks for the information.

Omkar

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

[rickrambo]

I have the similar issue ( need to convert integer - 2006026 into date,yyyy-mm-dd format IN THE TRANSFORMER.).
Can you please tell me what format have you changed to get it to work. Please also mention your input data type and output datatype.

Using 7.5.1 PX.
Thanks

[rickrambo]

Never mind I understood what you are saying.

[ray.wurlod]

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?

[kool78]

Hi

Try this in Modify Stage

TargetDate:date=date_from_string["%yyyy%mm%dd"](SampleString)


where TargetDate is your target column
and SampleString is your source date in string format 'yyyymmdd'

[mctny]

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.

[kris007]

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

Code: Select all

StringToTimestamp(%string%,[%"%mm/%dd/%yyyy %hh:%nn:%ss"%])

Or

StringToDate(%datestring%,[%"%mm/%dd/%yyyy"%])

based on the datatype in your output.

[mctny]

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

[kris007]

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

[mctny]

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

no the job doesnot give any ora error, just Datastage error, ok I will try your suggestion not exactly sure how to do though

but thank you very much again