MSSql 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 )

执行结果：

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

 

 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

 

转载于:https://www.cnblogs.com/xu8512/archive/2010/04/12/1710274.html