convert函数

convert函数
---------------------------
eg1:
日期20050720-> 2005/07/20
select convert(varchar(10),getdate(),111)

eg2:
12356.256 变成12,356.26
select convert(varchar,convert(money,12356.256),1)
 
eg3:
SELECT CONVERT(CHAR(10),CONVERT(DATETIME,'2005-10-20'),101)

----------
10/20/2005

eg4:
select replace(convert(varchar,getdate(),120),'-','/')

--结果
/*
-------------------------
2005/09/18 22:32:03

(1 row(s) affected)
*/

eg5:
select getdate()
2005-01-18 19:47:08.327
我要的结果是
20050118194708327
select convert(varchar(8),getdate(),112) + replace(convert(varchar(12),getdate(),114),':','')

逆向写
declare @t varchar(2000)
set @t='20051018102423'
select cast(STUFF(STUFF(left(@t,8),5,0,'-'),8,0,'-') +' '+STUFF(STUFF(right(@t,6),3,0,':'),6,0,':') as datetime)

 
eg6:
取长时间的时间(小时:分:秒)
select convert(varchar(12),getdate(),114)

 
eg7:
select convert(char(10),getdate(),120)     '短时间
select CAST(DATEPART(month, getdate()) AS VARCHAR(2)) + '-' + CAST(DATEPART(day, getdate()) AS VARCHAR(2)) from t1
select convert(char(15),getdate(),108)     'hh:mm:ss 
select convert(char(15),getdate(),114)     'hh:mm:ss:MS

eg8: 2005-10-14 17:02:00转变成 10-14 17:02
declare @t datetime
set @t='2005-10-14 17:02:00'
select substring(convert(varchar,@t,20),6,11)
結果:10-14 17:02

eg9:我现在要把字符型转成日期型
如字符：Jul  6 2005 12:00:00:000AM
该怎么写呢
SET LANGUAGE us_english
select convert(smalldatetime, 'Jul  6 2005 12:00:00:000AM')
-----------------------------------
SET LANGUAGE us_english
select convert(smalldatetime, 'Jul  6 2005 12:00:00:000AM')