Hi all,
I am facing an issue when comparing date fields.
I am doing like this
If Lkp_Read.EFF_DT_Target=Lkp_Read.EFF_DT_Input then 'I' Else 'U'
Lkp_Read.EFF_DT_Target,Lkp_Read.EFF_DT_Input are two nullable date fields.
If null is coming its giving warning as
Tfm_2,0: Conversion error calling conversion routine date_from_ustring data may have been lost
i tried using NULLToValue function also for assigning '' to date field but its not assigning empty to date field.
Please help me in this one
Thanks for your help
Regards
Swathi S
Comparing date fields
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 48
- Joined: Fri Feb 29, 2008 1:09 am
- Location: Bangalore
Comparing date fields
Thanks&Regards
S.Swathi
S.Swathi
Re: Comparing date fields
Try to pass all 0(zero) s into the date field in the correct date format which is used in you job,if the date field contains a null..swathi Singamareddygari wrote: i tried using NULLToValue function also for assigning '' to date field but its not assigning empty to date field.
Swathi S
Thanks,
Mohan.A.Jasti.
Mohan.A.Jasti.
-
- Premium Member
- Posts: 37
- Joined: Mon Dec 08, 2008 4:02 am
- Location: Milton Keynes, UK
You have to decide what date you would substitute for a null value. For strings, the empty string ("") is an obvious choice for null, and for numbers 0 is fairly obvious. But for dates you need to pick something like "1900-01-01" if you really must treat null as if it were a default value. date_from_ustring() will only work on a valid date in the string, and "" is not a valid date format.
So try using NullToValue(link.col,"1900-01-01")
(Of course you also need to consider why the values are coming as null in the first place, as in SQL there is a distinction between "" and 0 and null.)
So try using NullToValue(link.col,"1900-01-01")
(Of course you also need to consider why the values are coming as null in the first place, as in SQL there is a distinction between "" and 0 and null.)