Dates from strings using Date#() and handling invalid dates

[Jacob_Poole]

Background, there are trash dates stored as strings in the source data.  We're converting those values in the script using Date(Date#(datestring,'YYYYMMDD')). This morning  found an oddity.

Example:

Feb. 31st and  Sep. 31st would never be a valid date. 

=DATE(DATE#(20220231,'YYYYMMDD'))  evaluates to 03/03/2022  

=DATE(DATE#(20220931,'YYYYMMDD'))  evaluates to 10/01/2022

This makes sense considering the number of days passed since December 30, 1899 and the order of the calculations. 

Example:

NUM(DATE#(20220231,'YYYYMMDD') evaluates to 44623

DATE(01/01/1900 + 44623) evaluates to 03/03/2022

It just seems like there would be some check in the Date#() function for a valid date.  Further testing shows that it may be actually be doing a check, but for days between 1 and 31 in any given month.  

Example:

=DATE#(20220200,'YYYYMMDD') and =DATE#(20220232,'YYYYMMDD') appear not to evaluate.  

Is this intentional and working as designed? 

Does anyone have a way to account for this? 

Ideally I'd want these examples to not evaluate to a valid date is the string is not a valid date just as YYYYMM00 and YYYYMM32 do. 

Thanks in advance.

QlikView 

[MarcoWedel]

maybe you could check for validity e.g. like this:

If(Date(Date#(datestring,'YYYYMMDD'),'YYYYMMDD')=datestring,Date(Date#(datestring,'YYYYMMDD'))) as CorrectDate

hope this helps

Marco

[Jacob_Poole · Accepted solution]

I  added the text() function, and this idea seems to be working. 

EX:

=If(TEXT(Date(Date#(20220228,'YYYYMMDD'),'YYYYMMDD'))='20220228',Date(Date#(20220228,'YYYYMMDD'))) - evaluates

=If(TEXT(Date(Date#(20220231,'YYYYMMDD'),'YYYYMMDD'))='20220231',Date(Date#(20220231,'YYYYMMDD'))) - does not evaluate

Thanks Marco

[MarcoWedel]

glad it worked.

please close your thread by accepting a solution if your question is answered

thanks