Page 1 of 1
Comparing two timestamp fields in transformer
Posted: Thu Dec 09, 2010 1:36 pm
by devsonali
Hello Gurus
I am trying to compare two timestamp fields in a stage variable,
in.timestampfield < lkp.timestamp field ?
When I do a comparisons directly by above condition i am not seeing the desired result
I tried to search the forum with the key word "compare timestamp"
but could nt see any post addressing my issue
Any pointers ?
Thanks
Posted: Thu Dec 09, 2010 2:18 pm
by devsonali
Missed the timestamp format
Here is what both timestamps look like
eg
YYYY-MM-DD HH:MM:SS
Posted: Thu Dec 09, 2010 3:18 pm
by chulett
That external format should compare just fine. Can you provide your exact syntax, input values and output?
Posted: Thu Dec 09, 2010 4:16 pm
by devsonali
Hopefully i am understanding your question correctly
I have an input.timestamp field which i am comparing with a lookup.timestamp field
Both are having the same format
'YYYY-MM-DD HH24:MI:SS'
both the fields are pulled up by converting into char
TO_CHAR(inputtimestampfield, 'YYYY-MM-DD HH24:MI:SS'),
Posted: Thu Dec 09, 2010 4:19 pm
by ray.wurlod
Cool. Then they can be compared with comparison operators, for example
Code: Select all
InLink.Timestamp1 = InLink.Timestamp2
The ISO8601 format (which uses a 24 hour clock) for timestamps sorts and compares correctly when the timestamp is represented as a string.
Posted: Thu Dec 09, 2010 4:39 pm
by chulett
Meaning they should compare just fine.
What issue are you seeing? What makes you think that isn't happening properly? Can you provide some specific examples?
Posted: Thu Dec 09, 2010 6:09 pm
by devsonali
Ray/Creig
You are right, I had a wrong value in one of my query through which i was testing the result set
Sorry to waste your time
This works just fine
Marking it resolved
Posted: Thu Dec 09, 2010 7:26 pm
by chulett
No worries!