Jump to content
  • 0

Unable to filter on VARCHAR created by formula


randybow

Question

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.

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

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

 

Link to comment
Share on other sites

  • 0

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 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...