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 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)
执行结果:
类型值 | 转换结果 |
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://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.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