数据库三个函数总结

常用函数（示例放到查询分析器里面就能看到效果）

一、LEFT

返回字符串中从左边开始指定个数的字符。

示例：

declare @i int

declare @str varchar(10)

set @str='abcdefg'

set @i=4

select left(@str,@i) as '返回个字符串',

left(@str,@i-1) as '返回个字符串',

left(@str,@i-2) as '返回个字符串',

left(@str,@i-3) as '返回个字符串'

 

二、DATENAME
这个函数用来取时间的各个部分

 

示例：

declare @datetime varchar(20)

set @datetime='2008-09-24 12:43:22'

 

select DATENAME(yyyy,@datetime) as '年',DATENAME(MM,@datetime) as '月',

DATENAME(dd,@datetime) as '日',DATENAME(hh,@datetime) as '小时',

DATENAME(mi,@datetime) as '分钟',DATENAME(ss,@datetime) as '秒'

 

二、DATEDIFF
返回跨两个指定日期的日期边界数和时间边界数。(就时两个日期之间相差的时间)

 

示例：

 

declare @startdate varchar(20)

declare @enddate varchar(20)

set @startdate='2008-09-24 12:43:22'

set @enddate='2009-09-25 12:43:22'

select DATEDIFF ( yyyy , @startdate , @enddate ) as '相差年',

DATEDIFF ( mm , @startdate , @enddate ) as '相差月',

DATEDIFF ( dd , @startdate , @enddate ) as '相差日',

DATEDIFF ( hh , @startdate , @enddate ) as '相差小时',

DATEDIFF ( mi , @startdate , @enddate ) as '相差分钟',

DATEDIFF ( ss , @startdate , @enddate ) as '相差秒'