Comparing two timestamp fields in transformer

[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

[devsonali]

Missed the timestamp format
Here is what both timestamps look like
eg
YYYY-MM-DD HH:MM:SS

[chulett]

That external format should compare just fine. Can you provide your exact syntax, input values and output?

[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'),

[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.

[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?

[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

[chulett]

No worries!