Quick sight skips rows after refreshing the custom SQL due to date data type having dummy dates like "0001-12-30"

Question & Answer
1

Hi, Wanted to ask for a suggestion or work around on how i can change this data type to be included in my final output or results without quick sight skipping these rows due to an error in date format? In the some of the rows i have normal date formats like ‘2021-01-01’ where i have no issue but when the data is clunky and have dummy dates such as “0001-12-30” quick sight tends to exclude it. Is there a statement where i can use with SQL to include these rows without having to skip? I tried editing or format but it doesn’t give me an option to format it. Any help or suggestion here is appreciated.

Thanks
Abdul

2

Hello @Abdul_Mohammed,

One option could be to import the dates as string and then perform the conversion to date using a calculated field.

1 Like
3

Thanks Andres, i have tried using the parse date by converting the exisitng date function to string by using the following syntax Parsedate( date, ’ MM/dd/yyyy’) its coming up as blank or null. Can you please let me know if i am doing something wrong here?

4

Hi @Abdul_Mohammed - I believe 0001 may not be supported, but it is possible to change those data to a default date like 1900. Then you can create a calculate field and change those data .

Regards - Sanjeeb

5

The idea behind my suggestion is that your data is imported without skipping any rows and that the correct dates are converted to date in your dataset and not at the time of import. Therefore you should be using parsedate to convert your date from string to date.

When the parsedate function is returning proper dates when they are correctly formated, now you can deal with the “dummy” data with other functions like ifelse and convert those that return null into “proper” dummy dates.

I created a sample in Arena so you can see what I mean.

ParseDates

Hope this helps!

1 Like
6

Thanks both for the suggestions, will give it a try for both the cases today and reply back on the thread on what the outcomes is. Thanks

7

Hi both, the parsedate function was still causing an issue on my custom sql as rows were skipped when refreshed. As an alternative solution, I did a back end code update using NULLif and CAST functions to flag these dummy dates as NULL in my SQL and it worked for me. Now sql doesn’t skip any rows. Thank you both for your help