Dec 14, 2014

Sql server convert string to date

Sql server cast or convert to string is only possible for validate date format.

Conversion table for string to only date (Year in 4 digits)

Format
Example
Convert Script
MM/DD/YYYY
12/21/2014
CONVERT(DATETIME,'12/21/2014',101)
YYYY/MM/DD
2014/12/21
CONVERT(DATETIME,'2014/12/21',111)
DD/MM/YYYY
21/12/2014
CONVERT(DATETIME,'21/12/2014',103)
YYYY.MM.DD
2014.12.21
CONVERT(DATETIME,'2014.12.21',102)
DD.MM.YYYY
21.12.2014
CONVERT(DATETIME,'21.12.2014',104)
MM.DD.YYYY
12.21.2014
CONVERT(DATETIME,'12.21.2014',101)
MON DD YYYY
DEC 21 2014
CONVERT(DATETIME,'DEC 21 2014',0)
DD MON YYYY
21 DEC 2014
CONVERT(DATETIME,'21 DEC 2014',106)
YYYY MON DD
2014 DEC 21
CONVERT(DATETIME,'2014 DEC 21',102)
MON DD,YYYY
DEC 21,2014
CONVERT(DATETIME,'DEC 21,2014',107)
DD-MM-YYYY
21-12-2014
CONVERT(DATETIME,'21-12-2014',105)
MM-DD-YYYY
12-21-2014
CONVERT(DATETIME,'12-21-2014',110)
YYYY-MM-DD
2014-12-21
CONVERT(DATETIME,'2014-12-21',102)
YYYYMMDD
20141221
CONVERT(DATETIME,'20141221',112)

Conversion table for string to only date (Year in 2 digits)

Format
Example
Convert Script
MM/DD/YY
12/21/14
CONVERT(DATETIME,'12/21/14',1)
YY/MM/DD
14/12/21
CONVERT(DATETIME,'14/12/21',11)
DD/MM/YY
21/12/14
CONVERT(DATETIME,'21/12/14',3)
YY.MM.DD
14.12.21
CONVERT(DATETIME,'14.12.21',2)
DD.MM.YY
21.12.14
CONVERT(DATETIME,'21.12.14',4)
MON DD YY
DEC 21 14
CONVERT(DATETIME,'DEC 21 14',0)
DD NON YY
21 DEC 14
CONVERT(DATETIME,'21 DEC 14',6)
MON DD,YY
DEC 21,14
CONVERT(DATETIME,'DEC 21,14',7)
DD-MM-YY
21-12-14
CONVERT(DATETIME,'21-12-14',5)
MM-DD-YY
12-21-14
CONVERT(DATETIME,'12-21-14',10)
YYMMDD
141221
CONVERT(DATETIME,'141221',12)

Conversion table for string to date with time


Example
Convert Script
21/12/2014 05:30:45:555PM
CONVERT(DATETIME,'21/12/2014 05:30:45:555PM',131)
2014/12/21 05:30:45:555PM
CONVERT(DATETIME,'2014/12/21 05:30:45:555PM',111)
12/21/2014 05:30:45:555PM
CONVERT(DATETIME,'12/21/2014 05:30:45:555PM',101)
DEC 21 2014 02:30PM
CONVERT(DATETIME,'DEC 21 2014 02:30PM',0)
DEC 21 2014 05:30:45:555PM
CONVERT(DATETIME,'DEC 21 2014 05:30:45:555PM',109
DEC 21 14 05:30:45:555PM
CONVERT(DATETIME,'DEC 21 14 05:30:45:555PM',9)
21 DEC 2014 17:30:45:555
CONVERT(DATETIME,'21 DEC 2014 17:30:45:555',113)
21 DEC 14 17:30:45:555
CONVERT(DATETIME,'21 DEC 14 17:30:45:555',13)
2014 DEC 21 05:30:45:555PM
CONVERT(DATETIME,'2014 DEC 21 05:30:45:555PM',102)
DEC 21,2014 05:30:45:555PM
CONVERT(DATETIME,'DEC 21,2014 05:30:45:555PM',107)
2014-12-21 17:30:45
CONVERT(DATETIME,'2014-12-21 17:30:45',120)
2014-12-21 17:30:45
CONVERT(DATETIME,'2014-12-21 17:30:45',20)
2014-12-21 17:30:45.555
CONVERT(DATETIME,'2014-12-21 17:30:45.555',121)
2014-12-21 17:30:45:555
CONVERT(DATETIME,'2014-12-21 17:30:45:555',21)
2014-12-21T17:30:45.555
CONVERT(DATETIME,'2014-12-21T17:30:45.555',126)
2014-12-21T17:30:45.555Z
CONVERT(DATETIME,'2014-12-21T17:30:45.555Z',127)
12-21-2014 05:30:45:555PM
CONVERT(DATETIME,'12-21-2014 05:30:45:555PM',101)
21-12-2014 05:30:45:555PM
CONVERT(DATETIME,'21-12-2014 05:30:45:555PM',103)

No comments:

Post a Comment