I have a table updated by a scheduled task from an outside source. Its endDate field (type date/time) is only populated on a few records, with all the others importing empty. I can't change this behavior. I need to be able to filter a data view for records NOT having endDates before a user supplied value, for instance, 1/29/2020.
I can't use the imported endDate because most records have no value.... So, I have added a formula field, exitedDate that looks at the end date and copies it to the exitedDate field when not null. If null it writes the VARCHAR '6/11/2020' as a placeholder. I haven't been able to get filtering to work with on a user supplied date value.
Here is the formula calculating my date placeholder field, exitedDate:
CASE
WHEN [@field:endDate] Is Null THEN '06/01/2020'
ELSE CONVERT(VARCHAR(10), [@field:endDate], 101)
END
I am sure this is happening because of data type mismatches but I really need to be able to do this. Any help would be most appreciated.