Timestamp to Numeric value

[neeraj]

HI,

I have a requirement like... I want to convert my timestamp i.e. 2010-09-09 22:01:01 to a numeric field which I can use in my range look up.

I had found some functions in oracle and DataStage to convert date into numeric field but I could not find anything for Timestamp.

Is there any function available to do that...

Regards
Neeraj

[chulett]

What kind of 'numeric' value would you convert your sample timestamp to? 20100909220101? Something else?

[neeraj]

Hi,

I need to range lookup.

As commincated by my colleage, range lookup doesn't work on string . it works for numeric field and for unknown reasons, a date or timestamp field is considered as string.

So you need to transform your date or timestamps in a numeric field to get the correct results.

So I am getting start_dt_tm and end_dt_tm from my source which is a dataset and chg_dt_tm from my reference link which is coming from Database. I need to check chg_dt_tm between start_dt_tm and end_dt_tm.

The one solution which I have to add 2 tranformer before doing a lookup and split my record i.e. chg_dt_tm , start_dt_tm and end_dt_tm, in to date and time first and then use the functions JulianDayFromDate and SecondsFromTime and concatenate and store into integer and use it for lookup.

Please suggest.

Regards
Neeraj

[ray.wurlod]

Or you could just convert all the non-numeric characters to "" then store in a BigInt and proceed. Hence Craig's question about what format of number were you looking to use.