Dealing with different date formats

Cleaning Data in SQL Server Databases

Miriam Antona

Software Engineer

Different date formats

US English (month/day/year)
- 04/15/2008
Spanish (day/month/year)
- 15/04/2008
...

Checking the date format of our tables

SELECT *
FROM pilots

| pilot_code | pilot_name | pilot_surname | carrier_code | entry_date |
|------------|------------|---------------|--------------|------------|
| 1          | Thomas     | Peters        | HA           | 2011-10-01 |
| 2          | Hiroki     | Konoe         | MQ           | 2011-01-21 |
| 3          | Arturo     | Montero       | UA           | 2012-12-28 |
| 4          | David      | Captain       | US           | 2000-10-01 |
| 5          | Ainhoa     | Guerrera      | VX           | 2000-10-05 |
| 6          | Alvin      | Andersen      | OO           | 2012-01-15 |
| 7          | William    | Champy        | F9           | 2011-03-15 |
| ...        | ...        | ...           | ...          | ...        |

Format: yyyy-MM-dd

Functions to modify the date formats

CONVERT
FORMAT

CONVERT

CONVERT(data_type[(length)], expression [, style])

CONVERT

SELECT     
    CONVERT(VARCHAR(11), CAST(entry_date AS DATE), 0) AS '0',
    CONVERT(VARCHAR(10), CAST(entry_date AS DATE), 1) AS '1',
    CONVERT(VARCHAR(10), CAST(entry_date AS DATE), 101) AS '101',
    CONVERT(VARCHAR(10), CAST(entry_date AS DATE), 2) AS '2',
    CONVERT(VARCHAR(10), CAST(entry_date AS DATE), 120) AS '202'
FROM pilots

| 0           | 1        | 101        | 2        | 202        |
|-------------|----------|------------|----------|------------|
| Oct 1 2011  | 10/01/11 | 10/01/2011 | 11.10.01 | 2011-10-01 |
| Jan 21 2011 | 01/21/11 | 01/21/2011 | 11.01.21 | 2011-01-21 |
| Dec 28 2012 | 12/28/12 | 12/28/2012 | 12.12.28 | 2012-12-28 |
| Oct 1 2000  | 10/01/00 | 10/01/2000 | 00.10.01 | 2000-10-01 |
| ...         | ...      | ...        | ...      | ...        |

CONVERT

Without century (yy)	With century (yyyy)	Standard	Output
0	100	Default	mon dd yyyy hh:miAM (or PM)
1	101	U.S.	mm/dd/yyyy
2	102	ANSI	yyyy.mm.dd
3	103	British/French	dd/mm/yyyy
5	105	Italian	dd-mm-yyyy
...	...	...	...
10	110	USA	mm-dd-yyyy
12	112	ISO	yyyymmdd
...	...	...	...

FORMAT

FORMAT ( value, format [, culture ] )

More flexible than CONVERT
Worse performance
Not recommended for high data volume

FORMAT

SELECT FORMAT (CAST(entry_date AS DATE), 'd', 'en-US' ) AS 'US English Result',    
       FORMAT (CAST(entry_date AS DATE), 'd', 'de-de' ) AS 'German Result',
       FORMAT (CAST(entry_date AS DATE), 'D', 'en-US' ) AS 'US English Result',
       FORMAT (CAST(entry_date AS DATE), 'dd/MM/yyyy') AS 'DateTime Result' 
from pilots

| US English Result | German Result | US English Result         | DateTime Result |
|-------------------|---------------|---------------------------|-----------------|
| 10/1/2011         | 01.10.2011    | Saturday, October 1, 2011 | 01/10/2011      |
| 1/21/2011         | 21.01.2011    | Friday, January 21, 2011  | 21/01/2011      |
| 12/28/2012        | 28.12.2012    | Friday, December 28, 2012 | 28/12/2012      |
| ...               | ...           | ...                       | ...             |

Let's practice!

Cleaning Data in SQL Server Databases