sql server datetime转char的方法

 

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)

 

执行结果：

类型值	转换结果
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

union select 111,convert(nvarchar,getdate(),111)
 特殊情况：

1 转换为“YYMMDDHHmmss”格式

 

 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