《2022年SQL日期时间 .pdf》由会员分享，可在线阅读，更多相关《2022年SQL日期时间 .pdf（8页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、SQL日期时间-本周第一天SELECT DATEADD(Day,1-(DATEPART(Weekday,getdate()+DATEFIRST-1)%7,getdate()-or select dateadd(wk,datediff(wk,0,getdate(),0)-本周第一天select dateadd(wk,datediff(wk,0,getdate(),6)-上月第一天SELECT CONVERT(CHAR(10),DATEADD(month,-1,DATEADD(dd,-DAY(GETDATE()+1,GETDATE(),111)-上月最后一天SELECT CONVERT(CHAR(
2、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()
3、+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
4、,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(
5、)-本月第一个星期一SELECT DATEADD(wk,DATEDIFF(wk,DATEADD(dd,6-DAY(getdate(),getdate(),)-去年最后一天SELECT dateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate(),0)名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 8 页 -今年第一天SELECT DATEADD(yy,DATEDIFF(yy,0,getdate(),0)-今年最后一天SELECT dateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate()+1,0)-指定日期
6、所在周的任意一天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 d
7、atepart(week,getdate()as 年内的第几周-年内的第几季select datepart(quarter,getdate()as 年内的第几季-判断某天是当月的第几周的sql 函数CREATE FUNCTION WeekOfMonth(day datetime)RETURNS int AS begin-declare day datetime declare num int declare Start datetime declare dd int declare dayofweek char(8)declare dayofweek_num char(8)declare st
8、artWeekDays int-set day=2009-07-05 if datepart(dd,day)=1 return 1 else set Start=(SELECT DATEADD(mm,DATEDIFF(mm,0,day),0)-一个月第一天的set dayofweek=(datename(weekday,Start)-得到本月第一天是周几set dayofweek_num=(select(case dayofweek when 星期一 then 2 when 星期二 then 3 when 星期三 then 4 when 星期四 then 5 when 星期五 then 6 w
9、hen 星期六 then 7 名师资料总结-精品资料欢迎下载-名师精心整理-第 2 页，共 8 页 -when 星期日 then 1 end)set dayofweek_num=7-dayofweek_num+1-得到本月的第一周一共有几天-print dayofweek_num set dd=datepart(dd,day)-得到今天是这个月的第几天-print dd if dd=dayofweek_num-小于前一周的天数return 1 else set dd=dd-dayofweek_num if dd%7=0 begin set num=dd/7 return num+1 end e
10、lse-if dd%70 set num=dd/7 set num=num+1+1 return num end-常用日期的视图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()A
11、S 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 tomorrow FROM/*T-SQL:17 个与日期时间相关的自定义函数(UDF),周日作为周的最后一天,均不受 DateFirst、语言版本影响提示:(Datefirst+datepart(weekday,Date)%7 判断周几是最保险的!与 DateFirst 无关,与语言版
12、本无关DateFirst 可能会导致datepart(weekday,Date)不一样!无论 DateFirst 等于几,无论是什么语言版本的SQL Server 下面永远恒成立!(Datefirst+datepart(weekday,Date)%7:2、3、4、5、6、0、1 分别代表周一到周日-*/名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 8 页 -create function udf_GetAge(StartDate datetime,EndDate datetime)returns integer-返回精确年龄select dbo.udf_GetAge(1949-
13、10-01,getdate()begin return datediff(year,StartDate,EndDate)-case when datediff(day,dateadd(year,datediff(year,StartDate,EndDate),StartDate),EndDate)=0 then 0 else 1 end end go create function udf_DaysOfYearByDate(Date datetime)returns integer-返回年的天数可判断平(365)、润(366)年begin return datediff(day,dateadd
14、(year,datediff(year,0,Date),0),dateadd(year,datediff(year,0,Date)+1,0)end go create function udf_DaysOfYear(Year integer)returns integer-返回年的天数可判断平(365)、润(366)年begin return datediff(day,dateadd(year,year-year(0),0),dateadd(year,year-year(0)+1,0)end go create function udf_HalfDay(Date datetime)return
15、s datetime-返回 Date 是上午返回Date 的零点,Date 是下午返回Date 的十二点begin return case when datepart(hour,Date)12 then dateadd(day,datediff(day,0,Date),0)-上午归到零点else dateadd(hour,12,dateadd(day,datediff(day,0,Date),0)-下午归到十二点end end 名师资料总结-精品资料欢迎下载-名师精心整理-第 4 页，共 8 页 -go create function udf_WeekDiff(StartDate dateti
16、me,EndDate datetime)returns integer-返回 StartDate,EndDate 之间周数周日是当周的最后一天begin return datediff(week,StartDate,EndDate)-+1+case when(Datefirst+datepart(weekday,StartDate)%7=1 then 1 else 0 end-case when(Datefirst+datepart(weekday,EndDate)%7=1 then 1 else 0 end end go create function udf_WeekOfMonth(Dat
17、e datetime)-返回 Date 是所在月的第几周周日是当周的最后一天returns integer begin return datediff(week,case when(Datefirst+datepart(weekday,dateadd(month,datediff(month,0,Date),0)%7=1 then dateadd(month,datediff(month,0,Date),0)-1 else dateadd(month,datediff(month,0,Date),0)end,case when(Datefirst+datepart(weekday,Date)%
18、7=1 then Date-1 else Date)end-流水号编码方式-现实生活中的流水帐号的格式有五花八门，本来主要探讨一种比较普遍的格式2 位字符串+4位年月日期+4 位递增数字（如 MA2009090001），其他格式都不是问题。Declare SerialLong int,i int,ObjLeng int set SerialLong=4-定义最大的单号长度位数Declare NowDate varchar(10)名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 8 页 -set NowDate=GetDate()-取得本日完整日期Declare NowYear ch
19、ar(4),NowMonth char(2),NowDay char(2)set NowYear=year(NowDate)-取得年份set NowMonth=Month(NowDate)-取得月份set NowDay=Day(NowDate)-取得日期Declare FullYear char(4),fullMonth char(2),FullDay char(2),FullYMD char(8)set fullyear=NowYear-select fullyear if Len(NowMonth)=1-如果月份为 10 月以下则在月份前加0 Begin set FullMonth=0+N
20、owMonth End Else Begin set FullMonth=NowMonth end-select FullMonth-IF LEN(NOWDAY)=1-如果日期小于 10 则在前面加 0-BEGIN-SET FULLDAY=0+NOWDAY-END-Else-Begin-set FullDay=NowDay-End-组合今日的完整日期-set FullYmd=MA+fullyear+fullMonth+FullDay set FullYmd=MA+fullyear+fullMonth-select FullYmd Declare MaxSerial char(4)-取得最大的序
21、列号,如果为空时默认编为0:此处请改为自已对应表的对应字段进行查询select MaxSerial=isNull(SUBSTRING(MAX(UserID),9,4),0)From A_CY_AdminUser IF isnumeric(MaxSerial)=1-检查当前的流水号是否为数值Begin set MaxSerial=MaxSerial+1 set ObjLeng=Len(MaxSerial)IF ObjLeng SerialLong begin set i=0 While i (SerialLong-ObjLeng)-循环判断是否小于定义长度,小于则在前面+0 Begin set
22、MaxSerial=0+convert(varchar(4),MaxSerial)set i=i+1 名师资料总结-精品资料欢迎下载-名师精心整理-第 6 页，共 8 页 -IF i dt_end RETURN(DATEDIFF(Day,dt_begin,dt_end)+1-(SELECT COUNT(*)FROM tb_Holiday 名师资料总结-精品资料欢迎下载-名师精心整理-第 7 页，共 8 页 -WHERE HDate BETWEEN dt_begin AND dt_end)RETURN(-(DATEDIFF(Day,dt_end,dt_begin)+1-(SELECT COUNT
23、(*)FROM tb_Holiday WHERE HDate BETWEEN dt_end AND dt_begin)END GO if exists(select*from dbo.sysobjects where id=object_id(Ndbo.f_WorkDayADD)and xtype in(NFN,NIF,NTF)drop function dbo.f_WorkDayADD GO-在指定日期上增加工作天数CREATE FUNCTION f_WorkDayADD(date datetime,-基础日期workday int-要增加的工作日数)RETURNS datetime AS
24、BEGIN IF workday0 WHILE workday0 SELECT date=date+workday,workday=count(*)FROM tb_Holiday WHERE HDate BETWEEN date AND date+workday ELSE WHILE workday0 SELECT date=date+workday,workday=-count(*)FROM tb_Holiday WHERE HDate BETWEEN date AND date+workday RETURN(date)END 名师资料总结-精品资料欢迎下载-名师精心整理-第 8 页，共 8 页 -