How to handle date formatting of imported CSV file
I am receiving a CSV file from an outside service. The date fields are in the following format:
I don't have any historical data yet, as this is a new service. However, since the data I've received so far indicates that there will single digit and double digit days and months (no leading zeroes), I'm having a difficult time coming up with a way to import this data with properly formatted dates. I've tried the Date() function and the Date#() function, but the results are incorrect. The following screen shot shows the native data after it's imported to Qlik Sense:
Here's another screen shot showing the result of using the Date() function:
Any suggestions on how to handle this date format would be appreciated.
Thank you for the suggestion, but when I tried that Date# function, per your example, the resulting values were identical to the original values.
I took your suggestion a step further, and wrapped it in the Date() function as follows:
Date(Date#("Date Received",'MDDYYYY'),'M/DD/YYYY') as DateReceived,
This resulted in the following:
So, as you can see, the date looks good now. However, I anticipate there could be a problem when the number of digits in the month is 2 instead of 1, or the number of digits in the day is 1 instead of 2. Here's what happens if I change it to 'M/D/YYYY':
That suggestion is certainly getting me closer to a solution. However, it seems that more scenarios need to be considered, such as when the month has two digits and the day has one digit, the month as one digit and the day has one digit. I changed the order to Month/Day/Year, so the final format would be MM/DD/YYYY for US formatting. However, when the date is, for example, December 1, 2016, it will show up in the CSV file as 1212016, and the final result from the load script would be 1/21/2016.
Date(Date#("Date Received",'MMDDYYYY'),'MM/DD/YYYY'))) AS DateReceived,
Now I just need to be able to handle the case where the length of the date is 7, but the month could be single digit and the day double-digit, or the month could be double digit and the day is single digit.
That's a problem that I might only be able to resolve by asking the lab to format the data with a leading zero for months or days that are single-digit. There's no other way in the file to determine what the month actually is.
I think we've done all we can with the data we have to work with.