Unable to filter on VARCHAR created by formula

[randybow]

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.

[Vitalikssssss]

Hi @randybow,

The Date&Time comparison methods is not available in View if you use the formula field as far as I understood. 

You need to make sure that each logic block of your CASE expression returns Date&Time datatype.

Please try the following expression:

CASE

   WHEN [@field:endDate] Is Null THEN CONVERT(Datetime, '06/01/2020', 101)


   ELSE CONVERT(Datetime, [@field:endDate], 101)

END

Hope this helps.

Regards,

vitalikssssss

 

[randybow]

Using the CONVERT function to data type Datetime was exactly the solution. I had tried initially but couldn't find any detailed documentation on available data type keywords. Do you know if there is something better that the functions reference on the support site? It seems to be lacking in very many specific details.

Thanks for solving my problem.

Randy