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

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

Jacob_Poole — Fri, 20 May 2022 16:05:41 GMT

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.

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

MarcoWedel — Fri, 20 May 2022 18:21:58 GMT

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

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

Jacob_Poole — Fri, 20 May 2022 18:57:41 GMT

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

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

MarcoWedel — Fri, 20 May 2022 19:40:45 GMT

glad it worked.

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

thanks