May 25, 2016 at 9:54 am
I am working through a conditional split and need to account for NULL values. I have entered this expression:
((ISNULL(ItemReceived) ? "HUMPERDINK" : ItemReceived) != (ISNULL(LkUp_ItemReceived) ? "HUMPERDINK" : LkUp_ItemReceived))
and that works without any problems. But when I try to do one with a date value instead of text, it shows up as red and says it is not a valid expression. Here is the expression:
((ISNULL(CompleteDate) ? "1974-08-28 08:28:49.0000000 -01:00" : CompleteDate) != (ISNULL(LkUp_CompleteDate) ? "1974-08-28 08:28:49.0000000 -01:00" : LkUp_CompleteDate))
The data type for CompleteDate is DT_DBTIMESTAMPOFFSET. I have tried the above expression with and without the quotes, neither is working.
I'm sure I am missing something simple... can anyone see it?
thanks!
May 25, 2016 at 9:59 am
try casting your string (the datetime) to a datetime. From memory it's like (datetype_i_want)"mystring" but you can pick these from the function list anyways.
Steve.
May 25, 2016 at 10:45 am
Thanks Steve. I took a look at the casting functions and the one for DATETIMEOFFSET was confusing me. However, while looking around in there, I did see a NULL function of REPLACENULL. I had not used that before, I'm thinking maybe it is new. At least new since I started doing this. Instead of the Boolean NULL expression I was using, I used the REPLACENULL in the following expression:
(REPLACENULL(CompleteDate, "1974-08-28 08:28:49.0000000 -01:00") != (REPLACENULL(LkUp_CompleteDate, "1974-08-28 08:28:49.0000000 -01:00")))
This seems to be working for me. Thanks for getting me to poke around in the functions!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply