Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Jacob_Poole
Contributor III
Contributor III

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

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 

 

Labels (3)
1 Solution

Accepted Solutions
Jacob_Poole
Contributor III
Contributor III
Author

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

 

View solution in original post

3 Replies
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
Contributor III
Contributor III
Author

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