SQL string to date time

在SQL server中经常会转换string到datetime类型，最常用的函数就是Convert。那么对Convert这个函数，不得不详细的研究一下。Convert这个函数的功能很强大，格式又很简单CONVERT ( data_type [ ( length ) ] , expression [ , style ] )。单就将string到datetime类型的转换就有很多样式。如: Convert(datetime, expression), Convert(varchar(10), expression, 110)。 这里需要注意的是最后这个style的字段，这个字段的主要作用是限制时间格式的样式。具体列表如下：

SQL server的基本样式有

Standard Date Formats

Date Format

Standard

SQL Statement

Sample Output

Mon DD YYYY ¹
HH:MIAM (or PM)

Default

SELECT CONVERT(VARCHAR(20), GETDATE(), 100)

Jan 1 2005 1:29PM ¹

MM/DD/YY

USA

SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]

11/23/98

MM/DD/YYYY

USA

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]

11/23/1998

YY.MM.DD

ANSI

SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]

72.01.01

YYYY.MM.DD

ANSI

SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]

1972.01.01

DD/MM/YY

British/French

SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]

19/02/72

DD/MM/YYYY

British/French

SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]

19/02/1972

DD.MM.YY

German

SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]

25.12.05

DD.MM.YYYY

German

SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]

25.12.2005

DD-MM-YY

Italian

SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]

24-01-98

DD-MM-YYYY

Italian

SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]

24-01-1998

DD Mon YY ¹

-

SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]

04 Jul 06 ¹

DD Mon YYYY ¹

-

SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]

04 Jul 2006 ¹

Mon DD, YY ¹

-

SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]

Jan 24, 98 ¹

Mon DD, YYYY ¹

-

SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]

Jan 24, 1998 ¹

HH:MM:SS

-

SELECT CONVERT(VARCHAR(8), GETDATE(), 108)

03:24:53

Mon DD YYYY HH:MI:SS:MMMAM (or PM) ¹

Default + 
milliseconds

SELECT CONVERT(VARCHAR(26), GETDATE(), 109)

Apr 28 2006 12:32:29:253PM ¹

MM-DD-YY

USA

SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]

01-01-06

MM-DD-YYYY

USA

SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]

01-01-2006

YY/MM/DD

-

SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD]

98/11/23

YYYY/MM/DD

-

SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]

1998/11/23

YYMMDD

ISO

SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]

980124

YYYYMMDD

ISO

SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]

19980124

DD Mon YYYY HH:MM:SS:MMM(24h) ¹

Europe default + milliseconds

SELECT CONVERT(VARCHAR(24), GETDATE(), 113)

28 Apr 2006 00:34:55:190 ¹

HH:MI:SS:MMM(24H)

-

SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]

11:34:23:013

YYYY-MM-DD HH:MI:SS(24h)

ODBC Canonical

SELECT CONVERT(VARCHAR(19), GETDATE(), 120)

1972-01-01 13:42:24

YYYY-MM-DD HH:MI:SS.MMM(24h)

ODBC Canonical
(with milliseconds)

SELECT CONVERT(VARCHAR(23), GETDATE(), 121)

1972-02-19 06:35:24.489

YYYY-MM-DDTHH:MM:SS:MMM

ISO8601

SELECT CONVERT(VARCHAR(23), GETDATE(), 126)

1998-11-23T11:25:43:250

DD Mon YYYY HH:MI:SS:MMMAM ¹

Kuwaiti

SELECT CONVERT(VARCHAR(26), GETDATE(), 130)

28 Apr 2006 12:39:32:429AM ¹

DD/MM/YYYY HH:MI:SS:MMMAM

Kuwaiti

SELECT CONVERT(VARCHAR(25), GETDATE(), 131)

28/04/2006 12:39:32:429AM

那么在什么情况下，我们需要字段呢？

大家也知道在SQL Server中，时间字符串的格式是各式各样的，就像上表所示那样。那SQL server怎么去识别这些样式的时间格式呢？在通常情况下（一般是USA或Default），SQL server会自己分析时间字符串的格式。如： Convert(varchar(10), ‘2009-01-01’), convert(varchar(10), ‘20090101’), convert(varchar(10), ‘2009/01/01’)。所以，你可以不用指定style这个字段。但是在Italian（没有试过German/ British/French）环境下, 你必须显示的指定这个字段来转换时间， 而且还必须是非-号分隔符的字符串。主要的原因在于像USA, Italian, ODBC都是使用-来分隔的，并且他们的相同位置上对于的时间类别又不尽相同。所以，时常会发生转换varchar到datetime类型，超出值范围的错误。所以需要特别注意。比较简单的方法就是用Replace函数把-号替换掉，如”2009-01-11” -> “20090111”，因为后者是ISO的格式，所以我们就可以比较容易的像其他style转换，即使在不同的语言环境中也没问题。但是唯一麻烦的就是你的字符格式必须很固定，不能一会儿年月日，一会儿日月年。最好的办法还是用convert(datetime, expression)方式直接转到datetime类型。但是这种方式是全格式的时间格式，也就是说你不能控制只想取天或是月，所以还需要在外面套上一层convert(type, convert(datetime, expression), style)。另外的建议是，当我们要生成时间字符串从远端机器传输的时候，最好使用ISO格式YYYYMMDD HH:mm:ss，这样便于服务器端的转换。