Timestamp comparison

rsunny · Post by **rsunny** » Fri Sep 02, 2011 9:03 am

Hi,

I have a requirement where i need to compare the time stamp from source and Reference records and if the time stamp from the source is greater than Reference record, i need to pass the data.i am getting the time stamp from source and reference as '2011-07-21 03:24:46.000000 AM'.

When i gave the condition as Source time stamp>Reference Time Stamp, the condidtion is not satisfying and i am passing the records which i should not.Can anyone please help me in this issue .I tried to convert the Time stamp to Internal format by using Iconv but still not satisfying .

Thanks in Advance

Ravi.K · Post by **Ravi.K** » Fri Sep 02, 2011 9:06 am

Use Iconv function for both Source and Reference data then compare both values.

rsunny · Post by **rsunny** » Fri Sep 02, 2011 9:09 am

Ravi.K wrote:Use Iconv function for both Source and Reference data then compare both values.

I have used Iconv function but still not able to satisfy the condition.

chulett · Post by **chulett** » Fri Sep 02, 2011 9:29 am

If you have any control over the format of the timestamp from the source, make sure it is in military time. Strings just like you posted in 24 hour time will compare just fine.

rsunny · Post by **rsunny** » Fri Sep 02, 2011 9:43 am

Hi,

Basically what i did is i tried to convert the time stamp to 24hr format like '2011-02-21 15:24:46' and then used Iconv('2011-02-21','D-YMD[4,2,2]'):Iconv('15:24:46':'MTS') for comparison

Please let me know if i am doing anything wrong.

Thanks in advance

chulett · Post by **chulett** » Fri Sep 02, 2011 9:58 am

Show us every step... and you don't need to convert things twice. Heck, if you were successful in converting to 24 hour strings then (as I said) you can compare them directly.

sandeepgs · Post by **sandeepgs** » Fri Sep 02, 2011 1:14 pm

Hi,

I have seen this kind of issue the apt solution is as follows.

Read the time value as string and convert the AM/PM by adding +12 to the existing value i.e you are converting the time to a 24 hour time frame

Note: The condition can only applied in stage variables using transformer.

And then do the necessary calculations.

There are so many posts available in DSXchange on converting AM/PM format to 24 Hour format. go through them for more detail explination of business logic.

Regards,
Sandeepgs

ray.wurlod · Post by **ray.wurlod** » Sat Sep 03, 2011 2:34 am

ISO 8601 prescribes the format of a timestamp.

If your timestamps conform to this format, then you don't need to to anything other than compare them with a ">" operator.