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