A European supplier sends a file with dates written as 03/04/2026, meaning 3 April 2026. The import interprets the values as month/day, so many dates land in the wrong month and any value with a day above 12 is rejected. Which fix should the DBA apply?
Select an answer to reveal the explanation.
Short Explanation
Is 03/04 March 4th or April 3rd? Your database can't read the supplier's mind, so you have to tell it the format out loud. Once you parse with the right pattern and store a real date, every downstream query just works. The "swap only when the day's over 12" hack is a trap; it leaves every ambiguous date wrong.
Full Explanation
Dates in text files are ambiguous without an agreed format: 03/04/2026 is 4 March in month-first conventions and 3 April in day-first conventions. When the import assumes the wrong order, values with a day of 12 or less load silently into the wrong month, and values with a larger day fail because there is no thirteenth month. The correct fix is to parse the source column with an explicit day/month/year format during the transformation step and store the result in a proper DATE type, ideally agreeing with the supplier on an unambiguous format such as ISO 8601 for the future. Loading the values into a text column preserves the raw strings but pushes the problem onto every query and report, and it prevents date arithmetic, range filters and validation. Swapping day and month only when the day exceeds 12 fixes the rows that were failing but leaves every row with a day of 12 or less in the wrong month, which is the more dangerous silent error. Rejecting unconvertible rows and asking for a resend each night does not fix the misinterpreted rows that loaded and creates an ongoing manual burden. Exam caveat: also consider time zones and two-digit years when normalizing dates. Operational check: load a sample containing dates such as 03/04 and 25/12, confirm both parse to the intended calendar dates, and compare monthly totals with the supplier's report.