SQL日期语句大全--本周第一天SELECT DATEADD(Day,1-(DATEPART(Weekday,getdate())+@@DATEFIRST-1)%7,getdate())--orselect dateadd(wk, datediff(wk,0,getdate()), 0)--本周第一天select dateadd(wk, datediff(wk,0,getdate()), 6)--上月第一天SELECTCONVERT(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'--本月第一天select dateadd(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())))--orselect 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)--季度最后一天（直接推算法）SELECTDATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1+DATEPART(Quarter,getdate())*3-Month(g etdate()),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 datepart(quarter,getdate()) as 年内的第几季--判断某天是当月的第几周的sql函数CREATE FUNCTION WeekOfMonth(@day datetime)RETURNS intASbegin----declare @day datetimedeclare @num intdeclare @Start datetimedeclare @dd intdeclare @dayofweek char(8)declare @dayofweek_num char(8)declare @startWeekDays int---set @day='2009-07-05'if datepart(dd,@day)=1return 1elseset @Start= (SELECT DATEADD(mm, DATEDIFF(mm,0,@day), 0)) --一个月第一天的set @dayofweek= (datename(weekday,@Start)) ---得到本月第一天是周几set @dayofweek_num=(select (case @dayofweek when '星期一' then 2when '星期二' then 3when '星期三' then 4when '星期四' then 5when '星期五' then 6when '星期六' then 7when '星期日' then 1end))set @dayofweek_num= 7-@dayofweek_num+1 ---得到本月的第一周一共有几天---print @dayofweek_numset @dd=datepart(dd,@day) ----得到今天是这个月的第几天--print @ddif @dd<=@dayofweek_num --小于前一周的天数return 1elseset @dd=@dd-@dayofweek_numif @dd % 7=0beginset @num=@dd / 7return @num+1endelse --if @dd % 7<>0set @num=@dd / 7set @num=@num+1+1return @numend--常用日期的视图SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS today, REPLACE(CONVERT(varchar(8), GETDATE(), 108), ':', '') AS time,REPLACE(REPLACE(REPLACE(CONVERT(varchar, GETDATE(), 120), '-', ''), ' ', ''), ':', '') AS all_date, CONVERT(varchar(12), GETDATE(), 112) AS date,YEAR(GETDATE()) AS year, MONTH(GETDATE()) AS month, DAY(GETDATE()) AS day, CONVERT(varchar(8), DATEADD(d, - 1, GETDATE()), 112)AS yestaday, CONVERT(varchar(8), DATEADD(d, 1, GETDATE()), 112) AS tomorrowFROM/*T-SQL: 17 个与日期时间相关的自定义函数(UDF),周日作为周的最后一天,均不受@@DateFirst、语言版本影响提示:(@@Datefirst + datepart(weekday,@Date)) % 7 判断周几是最保险的! 与@@DateFirst 无关,与语言版本无关@@DateFirst 可能会导致datepart(weekday,@Date) 不一样!无论@@DateFirst 等于几,无论是什么语言版本的SQL Server 下面永远恒成立!(@@Datefirst + datepart(weekday,@Date))%7 : 2、3、4、5、6、0、1 分别代表周一到周日-- */create function udf_GetAge(@StartDate datetime,@EndDate datetime)returns integer-- 返回精确年龄select dbo.udf_GetAge('1949-10-01',getdate())beginreturn datediff(year,@StartDate,@EndDate)- case when datediff(day,dateadd(year,datediff(year,@StartDate,@EndDate),@StartDate),@EndDate) >= 0then 0else1endendgocreate function udf_DaysOfYearByDate(@Date datetime)returns integer-- 返回年的天数可判断平(365)、润(366) 年beginreturn datediff(day,dateadd(year,datediff(year,0,@Date),0),dateadd(year,datediff(year,0,@Date) + 1,0))endgocreate function udf_DaysOfYear(@Year integer)returns integer-- 返回年的天数可判断平(365)、润(366) 年beginreturn datediff(day,dateadd(year,@year - year(0),0),dateadd(year,@year - year(0) + 1,0))endgocreate function udf_HalfDay(@Date datetime)returns datetime-- 返回@Date 是上午返回@Date 的零点,@Date 是下午返回@Date 的十二点beginreturn case when datepart(hour,@Date) < 12then dateadd(day,datediff(day,0,@Date),0) --上午归到零点elsedateadd(hour,12,dateadd(day,datediff(day,0,@Date),0)) --下午归到十二点endendgocreate function udf_WeekDiff(@StartDate datetime,@EndDate datetime)returns integer-- 返回[@StartDate , @EndDate] 之间周数周日是当周的最后一天beginreturn datediff(week,@StartDate,@EndDate) -- + 1+ case when (@@Datefirst + datepart(weekday,@StartDate)) % 7 = 1then 1elseend- case when (@@Datefirst + datepart(weekday,@EndDate)) % 7 = 1then 1else 0endendgocreate function udf_WeekOfMonth(@Date datetime)-- 返回@Date 是所在月的第几周周日是当周的最后一天returns integerbeginreturn datediff(week,case when (@@Datefirst + datepart(weekday,dateadd(month,datediff(month,0,@Date),0))) % 7 = 1then dateadd(month,datediff(month,0,@Date),0) - 1elsedateadd(month,datediff(month,0,@Date),0)end,case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1then @Date-1else @Date)end--流水号编码方式--现实生活中的流水帐号的格式有五花八门，本来主要探讨一种比较普遍的格式2位字符串+4位年月日期+4位递增数字（如MA），其他格式都不是问题。