STRING TO INVALID TIMESTAMP CONVERSION

sankar.td · Post by **sankar.td** » Tue Apr 28, 2009 7:11 am

Hi All,

My job design is like this

SEQFILE--------TFM--------MLOADstage.

In this job i have a one column in file which is containing timestamp value
in the format 09020517090634.(YYYY-MM-DD:HH:MM:SS)

But few rows are coming with 09020517090668(YYYY-MM-DD:HH:MM:SS);
Rows containing this value 09020517090668 is populating in the target table as null value because of number of seconds is exceeding more than 60.

Now i want to populate this value 09020517090668 as 09020517090658 in the target table .how can i convert this rows?
Please help me if any body knows.

chulett · Post by **chulett** » Tue Apr 28, 2009 7:47 am

First off, the format is not "YYYY-MM-DD:HH:MM:SS" as there are no separators in your data. Second, you'd need to spell out your requirements in words, one example is not enough to go on. What is the business rule that needs to be enforced when the seconds are > 60?

sankar.td · Post by **sankar.td** » Tue Apr 28, 2009 8:13 am

HI Chulett,

My column format in target table is YYYYMMDDHHMISS.
i need seperators like YYYY-MM-DD HH:MM:SS.
For this already i have concatenated using oconv funtions in transformer stage.

Now my problem is

few records coming from source file for this column is containing seconds >60.
My business rule is i need to populate the data with seconds <60
If the records are coming with seconds >60 .

ray.wurlod · Post by **ray.wurlod** » Tue Apr 28, 2009 4:15 pm

Use a format mask.

Code: Select all

Fmt(InLink.TheString,"L####-##-## ##:##:##")

Reject records with the rightmost two characters >= "60". A constraint expression on the "good" output could be:

Code: Select all

Right(InLink.TheString,2) < 60

chulett · Post by **chulett** » Tue Apr 28, 2009 9:24 pm

sankar.td wrote:few records coming from source file for this column is containing seconds >60.
My business rule is i need to populate the data with seconds <60
If the records are coming with seconds >60 .

Sorry but that doesn't explain anything. How do you reduce the seconds to less than 60? What is the transformation business rule?

sankar.td · Post by **sankar.td** » Thu Apr 30, 2009 12:19 pm

Hi Chulet,

I have timestamp column in target table.For that column few records coming from source are like this
09020517090667
09020517090668

0902-05-17 09 :06 :68
(YYYY-MM-DD HH:MM:SS)

These records are populating as NULL values in my target table.Because seconds >60.

Now my business rule is
I want to populate the records having seconds >60 (0902-05-17 09 :06 :68)
as seconds<60 (0902-05-17 09 :06 :58)

That is my requirement?

ray.wurlod · Post by **ray.wurlod** » Thu Apr 30, 2009 4:18 pm

Gee, if you had a premium membership you could have read the answer to that in my previous post.

chulett · Post by **chulett** » Thu Apr 30, 2009 5:03 pm

And you still haven't explained the rule, just shown your single example again. OK, so 68 becomes 58... what about 67? 72? 99? What is the rule?

sankar.td · Post by **sankar.td** » Fri May 01, 2009 5:20 am

Hi chulet,

Sorry for Inconvienince.
My business rule is seconds> 60 i have to concatenate below 60.
That is any value like 58,59,57.........
Not only for 68.

Thanks & Regards,
Sankar.