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
TimeStamp with Microsecond
Moderators: chulett, rschirm, roy
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 62
- Joined: Thu Feb 08, 2007 6:01 am
- Location: Pune
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.
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.
I'm curious what you think was going on here? Helping. And on a Sunday for me no less.neeraj wrote:Request you to plz help..
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers