SQL Server 2005 Common Built-in Functions

SQL Server Built-in Functions.

-Replace的用法：

select replace('zhou','z','x')
select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
--20090331151201

-Date Format:

select CONVERT(varchar,getdate(),120)
--2009-03-31 15:10:02

select CONVERT(varchar(12) , getdate(), 111)
--2009/03/31

select CONVERT(varchar(12) , getdate(), 112)
--20090331

select CONVERT(varchar(12) , getdate(), 102)
--2009.03.31

select CONVERT(varchar(12) , getdate(), 108)
--15:13:26

 

 1 stuff()
select stuff('Zhou,',len('Zhou,'),1,'')//Zhou
select substring('zhou',1,3)//zho
select charindex('z','1111z1hou',1)//5
 2 nullif
 3 isnull
 4 len
 5 charindex
 6 substring
 7 ltrim
 8 rtrim
 9 cast --eg:select cast(1 as char(1))
10 convert
11 char(X) X =ASCII code --eg:print char(89)

 

 

 1getdate()
 2getutcdate()
 3datediff()
 4     select datediff(day,getdate(),dateadd(day,1,getdate()))
 5     datediff(day, t1,t2) --result=[t2-t1] on day level.
 6datepart:
 7     datepart(year,getdate()),
 8     datepart(month,getdate()),
 9     datepart(day,getdate())
10dateadd: select dateadd(year,1,getdate())