![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
select
1
,
convert
(
nvarchar
,
getdate
(),
1
)
union select 2 , convert ( nvarchar , getdate (), 2 )
union select 3 , convert ( nvarchar , getdate (), 3 )
union select 4 , convert ( nvarchar , getdate (), 4 )
union select 5 , convert ( nvarchar , getdate (), 5 )
union select 6 , convert ( nvarchar , getdate (), 6 )
union select 7 , convert ( nvarchar , getdate (), 7 )
union select 8 , convert ( nvarchar , getdate (), 8 )
union select 9 , convert ( nvarchar , getdate (), 9 )
union select 10 , convert ( nvarchar , getdate (), 10 )
union select 11 , convert ( nvarchar , getdate (), 11 )
union select 12 , convert ( nvarchar , getdate (), 12 )
union select 13 , convert ( nvarchar , getdate (), 13 )
union select 14 , convert ( nvarchar , getdate (), 14 )
union select 20 , convert ( nvarchar , getdate (), 20 )
union select 21 , convert ( nvarchar , getdate (), 21 )
union select 22 , convert ( nvarchar , getdate (), 22 )
union select 23 , convert ( nvarchar , getdate (), 23 )
union select 24 , convert ( nvarchar , getdate (), 24 )
union select 25 , convert ( nvarchar , getdate (), 25 )
union select 100 , convert ( nvarchar , getdate (), 100 )
union select 101 , convert ( nvarchar , getdate (), 101 )
union select 102 , convert ( nvarchar , getdate (), 102 )
union select 103 , convert ( nvarchar , getdate (), 103 )
union select 104 , convert ( nvarchar , getdate (), 104 )
union select 105 , convert ( nvarchar , getdate (), 105 )
union select 106 , convert ( nvarchar , getdate (), 106 )
union select 107 , convert ( nvarchar , getdate (), 107 )
union select 108 , convert ( nvarchar , getdate (), 108 )
union select 109 , convert ( nvarchar , getdate (), 109 )
union select 110 , convert ( nvarchar , getdate (), 110 )
union select 111 , convert ( nvarchar , getdate (), 111 )
union select 112 , convert ( nvarchar , getdate (), 112 )
union select 113 , convert ( nvarchar , getdate (), 113 )
union select 114 , convert ( nvarchar , getdate (), 114 )
union select 120 , convert ( nvarchar , getdate (), 120 )
union select 121 , convert ( nvarchar , getdate (), 121 )
union select 130 , convert ( nvarchar , getdate (), 130 )
union select 131 , convert ( nvarchar , getdate (), 131 )
union select 2 , convert ( nvarchar , getdate (), 2 )
union select 3 , convert ( nvarchar , getdate (), 3 )
union select 4 , convert ( nvarchar , getdate (), 4 )
union select 5 , convert ( nvarchar , getdate (), 5 )
union select 6 , convert ( nvarchar , getdate (), 6 )
union select 7 , convert ( nvarchar , getdate (), 7 )
union select 8 , convert ( nvarchar , getdate (), 8 )
union select 9 , convert ( nvarchar , getdate (), 9 )
union select 10 , convert ( nvarchar , getdate (), 10 )
union select 11 , convert ( nvarchar , getdate (), 11 )
union select 12 , convert ( nvarchar , getdate (), 12 )
union select 13 , convert ( nvarchar , getdate (), 13 )
union select 14 , convert ( nvarchar , getdate (), 14 )
union select 20 , convert ( nvarchar , getdate (), 20 )
union select 21 , convert ( nvarchar , getdate (), 21 )
union select 22 , convert ( nvarchar , getdate (), 22 )
union select 23 , convert ( nvarchar , getdate (), 23 )
union select 24 , convert ( nvarchar , getdate (), 24 )
union select 25 , convert ( nvarchar , getdate (), 25 )
union select 100 , convert ( nvarchar , getdate (), 100 )
union select 101 , convert ( nvarchar , getdate (), 101 )
union select 102 , convert ( nvarchar , getdate (), 102 )
union select 103 , convert ( nvarchar , getdate (), 103 )
union select 104 , convert ( nvarchar , getdate (), 104 )
union select 105 , convert ( nvarchar , getdate (), 105 )
union select 106 , convert ( nvarchar , getdate (), 106 )
union select 107 , convert ( nvarchar , getdate (), 107 )
union select 108 , convert ( nvarchar , getdate (), 108 )
union select 109 , convert ( nvarchar , getdate (), 109 )
union select 110 , convert ( nvarchar , getdate (), 110 )
union select 111 , convert ( nvarchar , getdate (), 111 )
union select 112 , convert ( nvarchar , getdate (), 112 )
union select 113 , convert ( nvarchar , getdate (), 113 )
union select 114 , convert ( nvarchar , getdate (), 114 )
union select 120 , convert ( nvarchar , getdate (), 120 )
union select 121 , convert ( nvarchar , getdate (), 121 )
union select 130 , convert ( nvarchar , getdate (), 130 )
union select 131 , convert ( nvarchar , getdate (), 131 )
执行结果:
类型值 | 转换结果 |
1 | 03/22/10 |
2 | 10.03.22 |
3 | 22/03/10 |
4 | 22.03.10 |
5 | 22-03-10 |
6 | 22 03 10 |
7 | 03 22, 10 |
8 | 10:42:54 |
9 | 03 22 2010 10:42:54:777AM |
10 | 03-22-10 |
11 | 10/03/22 |
12 | 100322 |
13 | 22 03 2010 10:42:54:777 |
14 | 10:42:54:777 |
20 | 2010-03-22 10:42:54 |
21 | 2010-03-22 10:42:54.777 |
22 | 03/22/10 10:42:54 AM |
23 | 2010-03-22 |
24 | 10:42:54 |
25 | 2010-03-22 10:42:54.777 |
特殊情况:
1 转换为“YYMMDDHHmmss”格式
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1
declare
@now
as
char
(
19
);
2 declare @year as char ( 2 );
3 declare @month as char ( 2 );
4 declare @day as char ( 2 );
5 declare @hour as char ( 2 );
6 declare @minute as char ( 2 );
7 declare @second as char ( 2 );
8 declare @YYMMDDHHmmss as char ( 12 );
9 set @now = convert ( char , getdate (), 20 );
10 set @year = substring ( @now , 3 , 2 );
11 set @month = substring ( @now , 6 , 2 );
12 set @day = substring ( @now , 9 , 2 );
13 set @hour = substring ( @now , 12 , 2 );
14 set @minute = substring ( @now , 15 , 2 );
15 set @second = substring ( @now , 18 , 2 );
16 set @YYMMDDHHmmss = @year + @month + @day + @hour + @minute + @second
2 declare @year as char ( 2 );
3 declare @month as char ( 2 );
4 declare @day as char ( 2 );
5 declare @hour as char ( 2 );
6 declare @minute as char ( 2 );
7 declare @second as char ( 2 );
8 declare @YYMMDDHHmmss as char ( 12 );
9 set @now = convert ( char , getdate (), 20 );
10 set @year = substring ( @now , 3 , 2 );
11 set @month = substring ( @now , 6 , 2 );
12 set @day = substring ( @now , 9 , 2 );
13 set @hour = substring ( @now , 12 , 2 );
14 set @minute = substring ( @now , 15 , 2 );
15 set @second = substring ( @now , 18 , 2 );
16 set @YYMMDDHHmmss = @year + @month + @day + @hour + @minute + @second