SQL日期语句大全.docx

SQL日期语句大全--本周第一天SELECT DATEADD(Day,1-(DATEPART(Weekday,getdate())+@@DATEFIRST-1)%7,getdate()) --or selectdateadd(wk, datediff(wk,0,getdate()), 0) --本周第一天selectdateadd(wk, datediff(wk,0,getdate()), 6) --上月第一天SELECT CONVERT(CHAR(10),DATEADD(month,-1,DATEADD(dd,-DAY(GETDATE())+1,GETDATE())),111) --上月最后一天SELECT CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()),0)),111)+ 23:59:59 --本月第一天selectdateadd(dd,-datepart(dd,getdate())+1,getdate()) --本月最后一天select dateadd(dd,-datepart(dd,getdate()) ,dateadd(mm,1,getdate())) --本月天数select datediff(dd,dateadd(dd,-datepart(dd,getdate())+1,getdate()), dateadd(dd,-datepart(dd,getdate())+1,dateadd(mm,1,getdate()))) --or select datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year(getdate()) as varchar)+-+cast(month(getdate()) as varchar)+-01 as datetime)))) --下月第一天select dateadd(dd,-datepart(dd,getdate())+1,dateadd(mm,1,getdate())) --下月最后一天SELECT CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+2,0)),111)+ 23:59:59 --季度第一天SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) --季度最后一天（直接推算法）SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1+DATEPART(Quarter,getdate())*3-Month(getdate()),getdate()),120)+1) --季度的最后一天（CASE判断法）select DATEADD(Month,DATEPART(Quarter,getdate())*3-Month(getdate()),getdate()) --本月第一个星期一SELECT DATEADD(wk, DATEDIFF(wk, , DATEADD(dd, 6 - DAY(getdate()), getdate())), ) --去年最后一天SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) --今年第一天SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) --今年最后一天SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1,0)) --指定日期所在周的任意一天SELECT DATEADD(Day,@number-DATEPART(Weekday,@dt),@dt)--5．指定日期所在周的任意星期几--A. 星期天做为一周的第1天SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt) --B. 星期一做为一周的第1天SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7-1,@dt) ---周内的第几日select datepart(weekday,getdate()) as 周内的第几日--年内的第几周select datepart(week,getdate()) as 年内的第几周--年内的第几季select datepar