sqlserver对时间月、周)的操作对时间(月的操作--上个月月初第一天selectCONVERT(varchar(12),DATEADD(mm,DATEDIFF(mm,0,dateadd(mm,-1,getdate())),0),112)---上个月月末最后一天selectCONVERT(varchar(12),dateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate()),0)),112)1.显示本月第一天显示本月第一天SELECTDATEADD(mm,DATEDIFF(mm,0,getdate()),0)selectconvert(datetime,convert(varchar(8),getdate(),120)+'01',120)2.显示本月最后一天显示本月最后一天selectdateadd(day,-1,convert(datetime,convert(varchar(8),dateadd(month,1,getdate()),120)+'01',120))SELECTdateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+1,0))3.上个月的最后一天上个月的最后一天SELECTdateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))4.本月的第一个星期一i本月的第一个星期一selectDATEADD(wk,DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())),0)5.本年的第一天本年的第一天SELECTDATEADD(yy,DATEDIFF(yy,0,getdate()),0)6.本年的最后一天本年的最后一天SELECTdateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0))7.去年的最后一天去年的最后一天SELECTdateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))8.本季度的第一天本季度的第一天SELECTDATEADD(qq,DATEDIFF(qq,0,getdate()),0)9.本周的星期一本周的星期一SELECTDATEADD(wk,DATEDIFF(wk,0,getdate()),0)10.查询本月的记录查询本月的记录select*fromtableNamewhereDATEPART(mm,theDate)=DATEPART(mm,GETDATE())andDATEPART(yy,theDate)=DATEPART(yy,GETDATE())11.查询本周的记录查询本周的记录select*fromtableNamewhereDATEPART(wk,theDate)=DATEPART(wk,GETDATE())andDATEPART(yy,theDate)=DATEPART(yy,GETDATE())12查询本季的记录注：其中:GETDATE()是获得系统时间的函数。select*fromtableNamewhereDATEPART(qq,theDate)=DATEPART(qq,GETDATE())andDATEPART(yy,theDate)=DATEPART(yy,GETDATE())13.获取当月总天数获取当月总天数:获取当月总天数selectDATEDIFF(dd,getdate(),DATEADD(mm,1,getdate()))selectdatediff(day,dateadd(mm,datediff(mm,'',getdate()),''),dateadd(mm,datediff(mm,'',getdate()),'1900-02-01'))14.获取当前为星期几获取当前为星期几DATENAME(weekday,getdate())-------------------------------------------------------------------------------------------1.selectCONVERT(varchar(12),getdate(),101)12/28/20072.selectCONVERT(varchar(12),getdate(),102)2007.12.283.selectCONVERT(varchar(12),getdate(),103)28/12/20074.selectCONVERT(varchar(12),getdate(),104)28.12.20075.selectCONVERT(varchar(12),getdate(),105)28-12-20076.selectCONVERT(varchar(12),getdate(),106)281220077.selectCONVERT(varchar(12),getdate(),107)1228,20078.selectCONVERT(varchar(12),getdate(),108)10:06:339.selectCONVERT(varchar(12),getdate(),109)12282007110.selectCONVERT(varchar(12),getdate(),110)12-28-200711.selectCONVERT(varchar(12),getdate(),111)2007/12/2812.selectCONVERT(varchar(12),getdate(),112)2007122813.selectCONVERT(varchar(12),getdate(),113)28122007114.selectCONVERT(varchar(12),getdate(),114)10:07:36:62315.selectCONVERT(varchar,getdate(),120)2007-12-2810:08:5616.selectreplace(replace(replace(CONVERT(varchar,getdate(),120),'-',''),'',''),':','')20071228101012
