TimeStamp with Microsecond

neeraj · Post by **neeraj** » Wed Nov 20, 2013 9:26 pm

Hello,

I need your help in resolving the timestamp issue being faced in my project.

If the Microsecond is more that 499 then time should be incremented by 1 as mentioned in the below example
Input------------------------------> Expected OUTPUT
2010-01-01 11:59:59.501--------->2010-01-02 12:00:00
2010-01-31 11:59:59.567--------->2010-02-01 12:00:00
2010-01-01 11:30:30.467--------->2010-01-01 11:30:30

Request you please let me know if the above mentioned output cn be achieved using the datastage functions.

Regards
Neeraj

chulett · Post by **chulett** » Wed Nov 20, 2013 10:52 pm

So... you want to round to the nearest second?

neeraj · Post by **neeraj** » Sun Nov 24, 2013 9:14 am

Yes.. I want round it to next possible level

chulett · Post by **chulett** » Sun Nov 24, 2013 9:17 am

What are your source and target? What are the actual data types involved? Mostly wondering if these are actually Timestamps or strings or some mixture of the two. I'd also be curious if you tried anything and what issues you've seen attempting to fulfill this requirement.

neeraj · Post by **neeraj** » Sun Nov 24, 2013 7:35 pm

The source is cab file and target is teradata. I tried couple of date functions I. The transformer stage but could not get the expected results.. Request you to plz help..

crystal_pup · Post by **crystal_pup** » Mon Nov 25, 2013 6:34 am

You can try something like this :-

Before populating the data into Teradata, use a case. Within the case , cast the said column into varchar and check for its last three substring values (and may be another cast is required to integer for comparison) if it's greater than 499. If it is greater than 499 then add 1 to the date else map the original value.

chulett · Post by **chulett** » Mon Nov 25, 2013 8:10 am

neeraj wrote:Request you to plz help..

I'm curious what you think was going on here? Helping. And on a Sunday for me no less.

I'm assuming you're already getting the string into a Timestamp. There are various functions you can look into to help here:

MicroSecondsFromTime to get the part you need to test.
TimetFromTimestamp to get something you can add a second to if ms > 499.
TimestampFromTimet to get back to a timestamp.

You can do this in stage variables or directly in the derivation by doing everything 'all at once'. Look up the syntax for these functions and note that the last two do not include the microseconds.