实用SQL语句精选日期函数.docVIP

实用SQL语句精选—日期函数 SELECT CAST(2003-05-08 12:35:29.998 AS smalldatetime); SELECT CAST(2003-05-08 12:35:29.999 AS smalldatetime); select cast(12 as char(2))+cast(13 as char(2)) select DATEPART(yy,GETDATE()) --返回日期中的年数 select DATEPART(mm,GETDATE()) select DATEPART(dd,GETDATE()) select DATEPART(hh,GETDATE()) select DATEPART(n,GETDATE()) --返回日期中的分钟数 select cast(DATEPART(hh,2003-05-08 20:35:29.999) as char(2))+:+cast(DATEPART(n,GETDATE()) as char(2)) select year(getdate()) select month(getdate()) select day(getdate()) select getdate() select left(getdate(),10) select right(getdate(),8) select left(CAST(getdate() AS smalldatetime),10) select right(CAST(getdate() AS smalldatetime),8) select DATEDIFF(dd, 8/1/2003, 8/8/2003) --计算间隔天数 select DATEDIFF(mm, 3/1/2011,GETDATE()) --返回间隔月数 select DATEDIFF(yy, 3/1/2010,GETDATE()) --返回间隔月数 select DATEDIFF(week, 7/1/2011,GETDATE()) --返回间隔周数 select DATEDIFF(hour, 7/21/2011,GETDATE()) --返回间隔月数 select DATEDIFF(dayofyear, 7/1/2011,GETDATE()) --返回间隔天数 select DATEDIFF(weekday, 7/1/2011,GETDATE()) --返回间隔天数 select DATEDIFF(minute, 7/21/2011,GETDATE()) select DATEDIFF(second, 7/21/2011,GETDATE()) SET DATEFORMAT mdy; --设置日期格式为mdy SET DATEFORMAT ymd; declare @start smalldatetime select @start=cast(cast(DATEPART(yy,GETDATE()) as char(4))+-+cast(DATEPART(mm,GETDATE()) as char(2))+-+cast(DATEPART(dd,GETDATE()) as char(2))+ +starttime as smalldatetime) from userpathinfo where pathid= 564 select @start select dateadd(month,2,@start) SELECT DATENAME(month, GETDATE()) AS Month Name SELECT DATENAME(weekday, GETDATE()) --返回星期名称，如星期三 select dateadd(month,-1,getdate()) --在向指定日期加上一段时间的基础上，返回新的datetime 值 select replace(大连XXXX公司大连XXXX办事处,大连,北京) select convert(varchar,getdate(),112) -- 如select cast(cast(DATEPART(yy,GETDATE()) as char(4))+-+cast((DATEPART(mm,GETDATE())-1) as char(2))+-+cast(1 as char(2)) as smalldatetime) if 5!=4 print y z else print y z --获得有关存储过程的信息 --若要显示Transact-SQL 存储过程的定义，请使用该过