执行下面格式化时间和日期的T-SQL脚本,在数据库查询分析器中示范了SQL Server中的大多数可用的时间数据格式
SELECT convert ( varchar , getdate (), 100 ) -- mon dd yyyy hh:mmAM (or PM)-- 10 31 2011 11 :33PM
SELECT convert ( varchar , getdate (), 101 ) -- mm/dd/yyyy - 10/31/2011
SELECT convert ( varchar , getdate (), 103 ) -- dd/mm/yyyy--31/10/2011
SELECT convert ( varchar , getdate (), 104 ) -- dd.mm.yyyy--31.10.2011
SELECT convert ( varchar , getdate (), 105 ) -- dd-mm-yyyy--31-10-2011
SELECT convert ( varchar , getdate (), 106 ) -- dd mon yyyy--31 10 2011
SELECT convert ( varchar , getdate (), 107 ) -- mon dd, yyyy--10 31, 2011
SELECT convert ( varchar , getdate (), 108 ) -- hh:mm:ss--23:36:24
SELECT convert ( varchar , getdate (), 109 ) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)
-- 10 31 2011 11:36:24:797PM
SELECT convert ( varchar , getdate (), 110 ) -- mm-dd-yyyy--10-31-2011
SELECT convert ( varchar , getdate (), 111 ) -- yyyy/mm/dd--2011/10/31
SELECT convert ( varchar , getdate (), 112 ) -- yyyymmdd--20111031
SELECT convert ( varchar , getdate (), 113 ) -- dd mon yyyy hh:mm:ss:mmm
-- 31 10 2011 23:36:24:797
SELECT convert ( varchar , getdate (), 114 ) -- hh:mm:ss:mmm(24h)--23:36:24:797
SELECT convert ( varchar , getdate (), 120 ) -- yyyy-mm-dd hh:mm:ss(24h)--2011-10-31 23:36:24
SELECT convert ( varchar , getdate (), 121 ) -- yyyy-mm-dd hh:mm:ss.mmm--2011-10-31T23:36:24.797
SELECT convert ( varchar , getdate (), 126 ) -- yyyy-mm-ddThh:mm:ss.mmm--2011-10-31T23:36:24.797
SELECT convert ( varchar ( 7 ), getdate (), 126 ) -- yyyy-mm --2011-10
SELECT right ( convert ( varchar , getdate (), 106 ), 8 ) -- mon yyyy-- 10 2011
CREATE FUNCTION dbo.fnFormatDate ( @Datetime DATETIME , @FormatMask VARCHAR ( 32 )) RETURNS VARCHAR ( 32 )
AS
BEGIN
DECLARE
@StringDate
VARCHAR
(
32
)
SET
@StringDate
=
@FormatMask
IF
(
CHARINDEX
(
'
YYYY
'
,
@StringDate
)
>
0
)
SET
@StringDate
=
REPLACE
(
@StringDate
,
'
YYYY
'
,
DATENAME
(YY,
@Datetime
))
IF
(
CHARINDEX
(
'
YY
'
,
@StringDate
)
>
0
)
SET
@StringDate
=
REPLACE
(
@StringDate
,
'
YY
'
,
RIGHT
(
DATENAME
(YY,
@Datetime
),
2
))
IF
(
CHARINDEX
(
'
Month
'
,
@StringDate
)
>
0
)
SET
@StringDate
=
REPLACE
(
@StringDate
,
'
Month
'
,
DATENAME
(MM,
@Datetime
))
IF
(
CHARINDEX
(
'
MON
'
,
@StringDate
COLLATE SQL_Latin1_General_CP1_CS_AS)
>
0
)
SET
@StringDate
=
REPLACE
(
@StringDate
,
'
MON
'
,
LEFT
(
UPPER
(
DATENAME
(MM,
@Datetime
)),
3
))
IF
(
CHARINDEX
(
'
Mon
'
,
@StringDate
)
>
0
)
SET
@StringDate
=
REPLACE
(
@StringDate
,
'
Mon
'
,
LEFT
(
DATENAME
(MM,
@Datetime
),
3
))
IF
(
CHARINDEX
(
'
MM
'
,
@StringDate
)
>
0
)
SET
@StringDate
=
REPLACE
(
@StringDate
,
'
MM
'
,
RIGHT
(
'
0
'
+
CONVERT
(
VARCHAR
,
DATEPART
(MM,
@Datetime
)),
2
))
IF
(
CHARINDEX
(
'
M
'
,
@StringDate
)
>
0
)
SET
@StringDate
=
REPLACE
(
@StringDate
,
'
M
'
,
CONVERT
(
VARCHAR
,
DATEPART
(MM,
@Datetime
)))
IF
(
CHARINDEX
(
'
DD
'
,
@StringDate
)
>
0
)
SET
@StringDate
=
REPLACE
(
@StringDate
,
'
DD
'
,
right
(
'
0
'
+
DATENAME
(DD,
@Datetime
),
2
))
IF
(
CHARINDEX
(
'
D
'
,
@StringDate
)
>
0
)
SET
@StringDate
=
REPLACE
(
@StringDate
,
'
D
'
,
DATENAME
(DD,
@Datetime
))
RETURN
@StringDate
END
GO
以前每次格式化日期都要通过字符串转换函数是转换,比较麻烦,像这样写成函数调用起来就方便多了:
示例：
SELECT dbo.fnFormatDate ( getdate (), ' MM/DD/YYYY ' ) -- 10/31/2011
