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
Timestamp to Numeric value
Moderators: chulett, rschirm, roy
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.