![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1
Create
function
FormatDateTime(
@Date
datetime
,
@formatStr
varchar
(
20
))
2 returns varchar ( 16 )
3 as
4 begin
5 declare @tempstr varchar ( 20 ), @index int , @retStr varchar ( 20 ), @formatLen int , @str1 varchar ( 6 ), @str2 varchar ( 6 ), @str3 varchar ( 6 ), @j int
6 declare @tempformat varchar ( 20 )
7 select @tempformat = @formatStr , @formatStr = Upper ( @formatStr ), @index =- 1 , @retstr = ''
8 if @formatStr = ' MM/DD/YYYY '
9 set @retstr = convert ( varchar ( 10 ), @date , 101 )
10 else if @formatstr = ' YYYY-MM-DD '
11 set @retstr = Convert ( char ( 10 ), @Date , 20 )
12 else if @formatStr = ' YYYY.MM.DD '
13 set @retstr = Convert ( varchar ( 10 ), @Date , 102 )
14 else if @formatStr = ' YYYY/MM/DD '
15 set @retstr = Convert ( varchar ( 10 ), @Date , 111 )
16 else if @formatStr = ' DD/MM/YYYY '
17 set @retstr = Convert ( varchar ( 10 ), @Date , 103 )
18 else if @formatStr = ' DD.MM.YYYY '
19 set @retstr = Convert ( varchar ( 10 ), @Date , 104 )
20 else if @formatStr = ' DD-MM-YYYY '
21 set @retstr = Convert ( varchar ( 10 ), @Date , 105 )
22 else if @formatStr = ' YYYYMMDD '
23 set @retstr = Convert ( varchar ( 10 ), @Date , 112 )
24 else
25 begin
26 select @tempformat = @formatStr , @formatLen = len ( @formatStr )
27 if @formatLen > 8
28 begin
29 set @index = charindex ( ' M ' , @tempformat )
30 select @str1 =right ( left ( @tempformat , @index - 1 ), @index - 5 ), @str2 =right ( @tempformat , @formatLen - @index - 1 )
31 select @index = charindex ( ' D ' , @str2 ), @str3 = @str2
32 set @str2 =left ( @str2 , @index - 1 )
33 set @str3 =right ( @str3 , len ( @str3 ) - @index - 1 )
34 end
35 select @tempstr = Convert ( char ( 10 ), @Date , 20 ), @str1 = isnull ( @str1 , '' ), @str2 = isnull ( @str2 , '' ), @str3 = isnull ( @str3 , '' ), @j = 0
36 while @index <> 0
37 begin
38 set @index = charindex ( ' - ' , @tempstr )
39 if @j = 0
40 select @retstr =left ( @tempstr , @index - 1 ) + @str1 , @j = @j + 1
41 else set @retstr = @retstr +left ( @tempstr , @index - 1 ) + @str2
42 select @tempstr =right ( @tempstr , len ( @tempstr ) - @index )
43 set @index = charindex ( ' - ' , @tempstr )
44 end
45 set @retstr = @retstr + @tempstr + @str3
46 end
47 return @retstr
48 end
2 returns varchar ( 16 )
3 as
4 begin
5 declare @tempstr varchar ( 20 ), @index int , @retStr varchar ( 20 ), @formatLen int , @str1 varchar ( 6 ), @str2 varchar ( 6 ), @str3 varchar ( 6 ), @j int
6 declare @tempformat varchar ( 20 )
7 select @tempformat = @formatStr , @formatStr = Upper ( @formatStr ), @index =- 1 , @retstr = ''
8 if @formatStr = ' MM/DD/YYYY '
9 set @retstr = convert ( varchar ( 10 ), @date , 101 )
10 else if @formatstr = ' YYYY-MM-DD '
11 set @retstr = Convert ( char ( 10 ), @Date , 20 )
12 else if @formatStr = ' YYYY.MM.DD '
13 set @retstr = Convert ( varchar ( 10 ), @Date , 102 )
14 else if @formatStr = ' YYYY/MM/DD '
15 set @retstr = Convert ( varchar ( 10 ), @Date , 111 )
16 else if @formatStr = ' DD/MM/YYYY '
17 set @retstr = Convert ( varchar ( 10 ), @Date , 103 )
18 else if @formatStr = ' DD.MM.YYYY '
19 set @retstr = Convert ( varchar ( 10 ), @Date , 104 )
20 else if @formatStr = ' DD-MM-YYYY '
21 set @retstr = Convert ( varchar ( 10 ), @Date , 105 )
22 else if @formatStr = ' YYYYMMDD '
23 set @retstr = Convert ( varchar ( 10 ), @Date , 112 )
24 else
25 begin
26 select @tempformat = @formatStr , @formatLen = len ( @formatStr )
27 if @formatLen > 8
28 begin
29 set @index = charindex ( ' M ' , @tempformat )
30 select @str1 =right ( left ( @tempformat , @index - 1 ), @index - 5 ), @str2 =right ( @tempformat , @formatLen - @index - 1 )
31 select @index = charindex ( ' D ' , @str2 ), @str3 = @str2
32 set @str2 =left ( @str2 , @index - 1 )
33 set @str3 =right ( @str3 , len ( @str3 ) - @index - 1 )
34 end
35 select @tempstr = Convert ( char ( 10 ), @Date , 20 ), @str1 = isnull ( @str1 , '' ), @str2 = isnull ( @str2 , '' ), @str3 = isnull ( @str3 , '' ), @j = 0
36 while @index <> 0
37 begin
38 set @index = charindex ( ' - ' , @tempstr )
39 if @j = 0
40 select @retstr =left ( @tempstr , @index - 1 ) + @str1 , @j = @j + 1
41 else set @retstr = @retstr +left ( @tempstr , @index - 1 ) + @str2
42 select @tempstr =right ( @tempstr , len ( @tempstr ) - @index )
43 set @index = charindex ( ' - ' , @tempstr )
44 end
45 set @retstr = @retstr + @tempstr + @str3
46 end
47 return @retstr
48 end
用法
select dbo. FormatDatetime(GetDate(),'YYYY年MM月DD日')
@formatStr格式串支持:
MM/DD/YYYY
YYYY-MM-DD
YYYY.MM.DD
YYYY/MM/DD
DD/MM/YYYY
DD.MM.YYYY
DD-MM-YYYY
YYYYMMDD或者
类似YYYY年MM月DD日
YYYY MM之间最多支持两个汉字,MM DD之间也最多支持两个个汉字
select dbo. FormatDatetime(GetDate(),'YYYY元年MM月份DD日')