T-SQL 常用日期格式

 

/*

Environment: win7 + sql server 2008 r2
Author: CC
Desctiption:

 常用日期格式
SQL Server中文版的默认的日期字段datetime格式是yyyy-mm-dd Thh:mm:ss.mmm
例如:
select getdate()
2012-07-17 14:48:49.913
*/

SELECT '----YY-MM-DD----' UNION ALL
SELECT CONVERT(VARCHAR(26), GETDATE(), 120 ) UNION ALL
SELECT CONVERT(VARCHAR(26), GETDATE(), 111 ) UNION ALL
SELECT CONVERT(VARCHAR(26), GETDATE(), 112 ) UNION ALL
SELECT CONVERT(VARCHAR(26), GETDATE(), 102 ) UNION ALL

SELECT '----MM-DD-YY----' UNION ALL
SELECT CONVERT(VARCHAR(26), GETDATE(), 101 ) UNION ALL
SELECT CONVERT(VARCHAR(26), GETDATE(), 110 ) UNION ALL
SELECT CONVERT(VARCHAR(26), GETDATE(), 107 ) UNION ALL
SELECT CONVERT(VARCHAR(26), GETDATE(), 109 ) UNION ALL

SELECT '----DD-MM-YY----' UNION ALL
SELECT CONVERT(VARCHAR(26), GETDATE(), 113 ) UNION ALL
SELECT CONVERT(VARCHAR(26), GETDATE(), 103 ) UNION ALL
SELECT CONVERT(VARCHAR(26), GETDATE(), 104 ) UNION ALL
SELECT CONVERT(VARCHAR(26), GETDATE(), 105 ) UNION ALL
SELECT CONVERT(VARCHAR(26), GETDATE(), 106 ) UNION ALL

SELECT '----HH-MI-SS----' UNION ALL
SELECT CONVERT(VARCHAR(26), GETDATE(), 108 ) UNION ALL
SELECT CONVERT(VARCHAR(26), GETDATE(), 114 ) 

 

----YY-MM-DD----
2012-07-17 14:53:10
2012/07/17
20120717
2012.07.17
----MM-DD-YY----
07/17/2012
07-17-2012
Jul 17, 2012
Jul 17 2012  2:53:10:147PM
----DD-MM-YY----
17 Jul 2012 14:53:10:147
17/07/2012
17.07.2012
17-07-2012
17 Jul 2012
----HH-MI-SS----
14:53:10
14:53:10:147