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

《实用SQL语句精选日期函数.doc》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。

1、实用SQL语句精选—日期函数SELECTCAST('2003-05-0812:35:29.998'ASsmalldatetime);SELECTCAST('2003-05-0812:35:29.999'ASsmalldatetime);selectcast(12aschar(2))+cast(13aschar(2))selectDATEPART("yy",GETDATE())--返回日期中的年数selectDATEPART("mm",GETDATE())selectDATEPART("dd",GETDAT

2、E())selectDATEPART("hh",GETDATE())selectDATEPART("n",GETDATE())--返回日期中的分钟数selectcast(DATEPART("hh",'2003-05-0820:35:29.999')aschar(2))+':'+cast(DATEPART("n",GETDATE())aschar(2))selectyear(getdate())selectmonth(getdate())selectday(getdate())selectgetdate()

3、selectleft(getdate(),10)selectright(getdate(),8)selectleft(CAST(getdate()ASsmalldatetime),10)selectright(CAST(getdate()ASsmalldatetime),8)selectDATEDIFF("dd",'8/1/2003','8/8/2003')--计算间隔天数selectDATEDIFF("mm",'3/1/2011',GETDATE())--返回间隔月数selectDATEDIFF("yy

4、",'3/1/2010',GETDATE())--返回间隔月数selectDATEDIFF("week",'7/1/2011',GETDATE())--返回间隔周数selectDATEDIFF("hour",'7/21/2011',GETDATE())--返回间隔月数selectDATEDIFF("dayofyear",'7/1/2011',GETDATE())--返回间隔天数selectDATEDIFF("weekday",'7/1/2011',GETDATE())--返回间隔天数selectDATED

5、IFF("minute",'7/21/2011',GETDATE())selectDATEDIFF("second",'7/21/2011',GETDATE())SETDATEFORMATmdy;--设置日期格式为mdySETDATEFORMATymd;declare@startsmalldatetimeselect@start=cast(cast(DATEPART("yy",GETDATE())aschar(4))+'-'+cast(DATEPART("mm",GETDATE())aschar(2))+

6、'-'+cast(DATEPART("dd",GETDATE())aschar(2))+''+starttimeassmalldatetime)fromuserpathinfowherepathid=564select@startselectdateadd(month,2,@start)SELECTDATENAME(month,GETDATE())AS'MonthName'SELECTDATENAME(weekday,GETDATE())--返回星期名称，如星期三selectdateadd(month,-

7、1,getdate())--在向指定日期加上一段时间的基础上，返回新的datetime值selectreplace('大连XXXX公司大连XXXX办事处','大连','北京')selectconvert(varchar,getdate(),112)--如：20110902selectcast(cast(DATEPART("yy",GETDATE())aschar(4))+'-'+cast((DATEPART("mm",GETDATE())-1)aschar(2))+'-'+cast(1aschar(2))

8、assmalldatetime)if5!=4print'y>z'elseprint'y