{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"dates-in-five-formats","title":"Parsing dates that were typed by people","url":"https://addaly.com/learn/data-and-sql/dates-in-five-formats","position":47,"of":66,"minutes":9,"answer":"A slash-separated date is ambiguous unless some value has a day above 12, so test the column before choosing a pattern, parse in a view, and check the minimum, maximum and month histogram afterwards.","keyIdea":"A slash-separated date is ambiguous unless some value has a day above 12, so test the column before choosing a pattern, parse in a view, and check the minimum, maximum and month histogram afterwards."},"body":{"format":"markdown","text":"## 03/04/2026 is two dates\n\nA file from a British or Indian system means 3 April. A file from an American one means 4 March. The file does not say which, and neither does the column name. Parsing it with the wrong pattern does not produce errors for the first twelve days of every month, because 03/04 is valid either way. It produces wrong dates, silently, for about forty percent of the rows, and correct ones for the rest.\n\n## Let the data tell you, when it can\n\nIf any value in the column has a first part greater than 12, the first part cannot be a month, so the format is day-first:\n\n```sql\nSELECT count(*) FILTER (WHERE split_part(paid_on, '/', 1)::int > 12) AS first_part_over_12,\n       count(*) FILTER (WHERE split_part(paid_on, '/', 2)::int > 12) AS second_part_over_12\nFROM raw_payments;\n```\n\nOne of those counts should be zero and the other should not. If the first is 3,400 and the second is 0, the file is day-first. If both are zero, which happens on a small file or one covering only the first twelve days of a month, the data cannot tell you, and the only honest step is to ask the person who exported it. A mixed file, where both counts are non-zero, means two formats in one column, usually because two people exported from two systems, and the `load_file` column from the previous lesson is how you find which rows came from where.\n\n## The parsing functions\n\nPostgres: `to_date(paid_on, 'DD/MM/YYYY')`. The pattern letters are `DD` day, `MM` month, `YYYY` four-digit year, `YY` two-digit year, `HH24:MI:SS` for times, and `to_timestamp` when a time is present. Modern Postgres rejects an impossible date such as 31 February with an error rather than rolling it forward, which is what you want.\n\nDuckDB: `strptime(paid_on, '%d/%m/%Y')`, with C-style codes. MySQL: `STR_TO_DATE(paid_on, '%d/%m/%Y')`. SQLite has no parser at all; it expects ISO text and you rearrange the pieces with `substr`: `substr(d, 7, 4) || '-' || substr(d, 4, 2) || '-' || substr(d, 1, 2)`, which is ugly and works.\n\n## Two-digit years\n\n`26` is 2026 to a human reading a recent file and 1926 to a birth-date column. Postgres's `YY` maps 00 to 69 onto 2000 to 2069 and 70 to 99 onto 1970 to 1999. A customer born in 1968 is parsed as born in 2068. For any column that can hold dates more than fifty years apart, do not accept two-digit years; find the source that has four.\n\n## Spreadsheet serial numbers\n\nA date column exported from Excel or Sheets sometimes arrives as `45000`. That is not a code; it is the number of days since 30 December 1899, which is how spreadsheets store dates internally. `date '1899-12-30' + 45000` is 15 March 2023. The anchor is 30 December rather than 31 because of a leap-year bug in Lotus 1-2-3 that Excel reproduced on purpose in 1987 and has kept ever since. A column that mixes serial numbers and typed text needs a `CASE` that tests whether the value is all digits and parses each kind its own way.\n\n## Times, and the parts people leave out\n\n`14:30` and `2:30 PM` are the same time in two formats; `HH24:MI` and `HH12:MI AM` in Postgres's pattern language. A value like `2026-03-04` with no time is midnight, and a range filter on it behaves as the grouping-by-time lesson described. A value like `24:00` is rejected by Postgres and accepted by some systems as the end of the day. A value with a trailing `Z` is UTC; one with `+05:30` is an offset; one with neither is a wall-clock reading whose zone lives in somebody's head, which the earlier lesson on timestamps covered.\n\n## The check that stays\n\nAfter parsing, look at the distribution:\n\n```sql\nSELECT min(paid_on), max(paid_on), count(*) FILTER (WHERE paid_on IS NULL)\nFROM payments_typed;\n```\n\nA minimum of 1 January 1970 means a zero was parsed as a date. A maximum in the future means a day-first file was read month-first, or a two-digit year went the wrong way. A NULL count above zero is the list of values still to be decided. Add a histogram by month and read it: a file covering March with a dozen rows scattered across the other eleven months is a file with a format problem on those dozen rows, and they are usually all from one source."},"check":{"question":"A file's `order_date` column contains values like `03/04/2026`. A test shows no value with a first part above 12 and no value with a second part above 12. The analyst parses it as day-first because the customer is in Mumbai. What is the status of that decision?","options":[{"text":"Correct and verified, because the absence of values above 12 in either position proves both parts are within valid month range and the locale settles the order.","correct":false,"tempting":"The test proved only that the data cannot distinguish the formats; the locale is an assumption about the exporting system, not evidence from the file."},{"text":"Wrong, because a file with no day above 12 must be month-first, since day-first files always contain days in the teens and twenties.","correct":false,"tempting":"A short file, or one covering the first twelve days of a month, can be day-first and still contain no day above 12."},{"text":"Unverified; the data cannot decide it, and it should be confirmed with whoever exported the file before any date-based figure is reported.","correct":true},{"text":"Irrelevant, because to_date raises an error for any row parsed with the wrong pattern, so a successful parse confirms the format.","correct":false,"tempting":"Both patterns produce valid dates for these values, so neither raises an error and a clean parse proves nothing."}]},"prev":{"slug":"everything-as-text-first","title":"Stage as text, then cast in a query you can re-run","url":"https://addaly.com/learn/data-and-sql/everything-as-text-first"},"next":{"slug":"strings-trim-case-and-unicode","title":"Text that looks identical and is not","url":"https://addaly.com/learn/data-and-sql/strings-trim-case-and-unicode"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Parsing dates that were typed by people', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/dates-in-five-formats (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Parsing dates that were typed by people","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":5,"slug":"data-that-arrives-dirty","title":"Data that arrives dirty"},"answer":null,"keyIdea":"A slash-separated date is ambiguous unless some value has a day above 12, so test the column before choosing a pattern, parse in a view, and check the minimum, maximum and month histogram afterwards.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"03-04-2026-is-two-dates","runs":[{"text":"03/04/2026 is two dates"}]},{"t":"p","runs":[{"text":"A file from a British or Indian system means 3 April. A file from an American one means 4 March. The file does not say which, and neither does the column name. Parsing it with the wrong pattern does not produce errors for the first twelve days of every month, because 03/04 is valid either way. It produces wrong dates, silently, for about forty percent of the rows, and correct ones for the rest."}]},{"t":"h2","id":"let-the-data-tell-you-when-it-can","runs":[{"text":"Let the data tell you, when it can"}]},{"t":"p","runs":[{"text":"If any value in the column has a first part greater than 12, the first part cannot be a month, so the format is day-first:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT count(*) FILTER (WHERE split_part(paid_on, '/', 1)::int > 12) AS first_part_over_12,\n       count(*) FILTER (WHERE split_part(paid_on, '/', 2)::int > 12) AS second_part_over_12\nFROM raw_payments;"},{"t":"p","runs":[{"text":"One of those counts should be zero and the other should not. If the first is 3,400 and the second is 0, the file is day-first. If both are zero, which happens on a small file or one covering only the first twelve days of a month, the data cannot tell you, and the only honest step is to ask the person who exported it. A mixed file, where both counts are non-zero, means two formats in one column, usually because two people exported from two systems, and the "},{"text":"load_file","code":true},{"text":" column from the previous lesson is how you find which rows came from where."}]},{"t":"h2","id":"the-parsing-functions","runs":[{"text":"The parsing functions"}]},{"t":"p","runs":[{"text":"Postgres: "},{"text":"to_date(paid_on, 'DD/MM/YYYY')","code":true},{"text":". The pattern letters are "},{"text":"DD","code":true},{"text":" day, "},{"text":"MM","code":true},{"text":" month, "},{"text":"YYYY","code":true},{"text":" four-digit year, "},{"text":"YY","code":true},{"text":" two-digit year, "},{"text":"HH24:MI:SS","code":true},{"text":" for times, and "},{"text":"to_timestamp","code":true},{"text":" when a time is present. Modern Postgres rejects an impossible date such as 31 February with an error rather than rolling it forward, which is what you want."}]},{"t":"p","runs":[{"text":"DuckDB: "},{"text":"strptime(paid_on, '%d/%m/%Y')","code":true},{"text":", with C-style codes. MySQL: "},{"text":"STR_TO_DATE(paid_on, '%d/%m/%Y')","code":true},{"text":". SQLite has no parser at all; it expects ISO text and you rearrange the pieces with "},{"text":"substr","code":true},{"text":": "},{"text":"substr(d, 7, 4) || '-' || substr(d, 4, 2) || '-' || substr(d, 1, 2)","code":true},{"text":", which is ugly and works."}]},{"t":"h2","id":"two-digit-years","runs":[{"text":"Two-digit years"}]},{"t":"p","runs":[{"text":"26","code":true},{"text":" is 2026 to a human reading a recent file and 1926 to a birth-date column. Postgres’s "},{"text":"YY","code":true},{"text":" maps 00 to 69 onto 2000 to 2069 and 70 to 99 onto 1970 to 1999. A customer born in 1968 is parsed as born in 2068. For any column that can hold dates more than fifty years apart, do not accept two-digit years; find the source that has four."}]},{"t":"h2","id":"spreadsheet-serial-numbers","runs":[{"text":"Spreadsheet serial numbers"}]},{"t":"p","runs":[{"text":"A date column exported from Excel or Sheets sometimes arrives as "},{"text":"45000","code":true},{"text":". That is not a code; it is the number of days since 30 December 1899, which is how spreadsheets store dates internally. "},{"text":"date '1899-12-30' + 45000","code":true},{"text":" is 15 March 2023. The anchor is 30 December rather than 31 because of a leap-year bug in Lotus 1-2-3 that Excel reproduced on purpose in 1987 and has kept ever since. A column that mixes serial numbers and typed text needs a "},{"text":"CASE","code":true},{"text":" that tests whether the value is all digits and parses each kind its own way."}]},{"t":"h2","id":"times-and-the-parts-people-leave-out","runs":[{"text":"Times, and the parts people leave out"}]},{"t":"p","runs":[{"text":"14:30","code":true},{"text":" and "},{"text":"2:30 PM","code":true},{"text":" are the same time in two formats; "},{"text":"HH24:MI","code":true},{"text":" and "},{"text":"HH12:MI AM","code":true},{"text":" in Postgres’s pattern language. A value like "},{"text":"2026-03-04","code":true},{"text":" with no time is midnight, and a range filter on it behaves as the grouping-by-time lesson described. A value like "},{"text":"24:00","code":true},{"text":" is rejected by Postgres and accepted by some systems as the end of the day. A value with a trailing "},{"text":"Z","code":true},{"text":" is UTC; one with "},{"text":"+05:30","code":true},{"text":" is an offset; one with neither is a wall-clock reading whose zone lives in somebody’s head, which the earlier lesson on timestamps covered."}]},{"t":"h2","id":"the-check-that-stays","runs":[{"text":"The check that stays"}]},{"t":"p","runs":[{"text":"After parsing, look at the distribution:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT min(paid_on), max(paid_on), count(*) FILTER (WHERE paid_on IS NULL)\nFROM payments_typed;"},{"t":"p","runs":[{"text":"A minimum of 1 January 1970 means a zero was parsed as a date. A maximum in the future means a day-first file was read month-first, or a two-digit year went the wrong way. A NULL count above zero is the list of values still to be decided. Add a histogram by month and read it: a file covering March with a dozen rows scattered across the other eleven months is a file with a format problem on those dozen rows, and they are usually all from one source."}]}],"check":{"question":[{"text":"A file’s "},{"text":"order_date","code":true},{"text":" column contains values like "},{"text":"03/04/2026","code":true},{"text":". A test shows no value with a first part above 12 and no value with a second part above 12. The analyst parses it as day-first because the customer is in Mumbai. What is the status of that decision?"}],"options":[{"text":[{"text":"Correct and verified, because the absence of values above 12 in either position proves both parts are within valid month range and the locale settles the order."}],"correct":false,"tempting":[{"text":"The test proved only that the data cannot distinguish the formats; the locale is an assumption about the exporting system, not evidence from the file."}]},{"text":[{"text":"Wrong, because a file with no day above 12 must be month-first, since day-first files always contain days in the teens and twenties."}],"correct":false,"tempting":[{"text":"A short file, or one covering the first twelve days of a month, can be day-first and still contain no day above 12."}]},{"text":[{"text":"Unverified; the data cannot decide it, and it should be confirmed with whoever exported the file before any date-based figure is reported."}],"correct":true,"tempting":null},{"text":[{"text":"Irrelevant, because to_date raises an error for any row parsed with the wrong pattern, so a successful parse confirms the format."}],"correct":false,"tempting":[{"text":"Both patterns produce valid dates for these values, so neither raises an error and a clean parse proves nothing."}]}],"order":[0,2,3,1],"why":null},"prev":{"slug":"everything-as-text-first","title":"Stage as text, then cast in a query you can re-run","minutes":8},"next":{"slug":"strings-trim-case-and-unicode","title":"Text that looks identical and is not","minutes":9}}}