处理不同的日期格式

在 SQL Server 数据库中清洗数据

Miriam Antona

Software Engineer

不同的日期格式

美式英语（月/日/年）
- 04/15/2008
西班牙语（日/月/年）
- 15/04/2008
...

检查表的日期格式

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 |
| ...        | ...        | ...           | ...          | ...        |

格式：yyyy-MM-dd

修改日期格式的函数

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

无世纪（yy）	含世纪（yyyy）	标准	输出
0	100	默认	mon dd yyyy hh:miAM（或 PM）
1	101	美国	mm/dd/yyyy
2	102	ANSI	yyyy.mm.dd
3	103	英国/法国	dd/mm/yyyy
5	105	意大利	dd-mm-yyyy
...	...	...	...
10	110	美国	mm-dd-yyyy
12	112	ISO	yyyymmdd
...	...	...	...

FORMAT

FORMAT ( value, format [, culture ] )

比 CONVERT 更灵活
性能较差
不建议用于大数据量

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      |
| ...               | ...           | ...                       | ...             |

Vamos praticar!

在 SQL Server 数据库中清洗数据