- 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(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())))
- --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 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 @startWeekDays 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
- when '星期六' then 7
- 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
- else --if @dd % 7<>0
- 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()) 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 tomorrow
- FROM
- /*
- 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())
- 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(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)
- returns 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
- go
- create function udf_WeekDiff(@StartDate datetime,@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(@Date 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)) % 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)
- set @NowDate = GetDate() --取得本日完整日期
- Declare @NowYear char(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' + @NowMonth
- 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)
- --取得最大的序列号,如果为空时默认编为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 @MaxSerial = '0' + convert(varchar(4),@MaxSerial)
- set @i = @i + 1
- IF @i < (@SerialLong-@ObjLeng)
- continue
- Else
- break
- End
- End
- End
- IF len(@MaxSerial) = @SerialLong --判断序列号是否已满9999
- Begin
- Declare @FullSerial char(12)
- set @FullSerial = @FullYMD + @MaxSerial --组合新的完整编号
- End
- else
- Begin
- set @FullSerial = ''
- End
- --Select @FullSerial
- --假日表
- if exists (select * from dbo.sysobjects where id = object_id(N'[tb_Holiday]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
- drop table [tb_Holiday]
- GO
- --定义节假日表
- CREATE TABLE tb_Holiday(
- HDate smalldatetime primary key clustered, --节假日期
- Name nvarchar(50) not null) --假日名称
- GO
- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDay]') and xtype in (N'FN', N'IF', N'TF'))
- drop function [dbo].[f_WorkDay]
- GO
- --计算两个日期之间的工作天数
- CREATE FUNCTION f_WorkDay(
- @dt_begin datetime, --计算的开始日期
- @dt_end datetime --计算的结束日期
- )RETURNS int
- AS
- BEGIN
- IF @dt_begin>@dt_end
- RETURN(DATEDIFF(Day,@dt_begin,@dt_end)
- +1-(
- SELECT COUNT(*) FROM tb_Holiday
- WHERE HDate BETWEEN @dt_begin AND @dt_end))
- RETURN(-(DATEDIFF(Day,@dt_end,@dt_begin)
- +1-(
- SELECT COUNT(*) FROM tb_Holiday
- WHERE HDate BETWEEN @dt_end AND @dt_begin)))
- END
- GO
- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDayADD]') and xtype in (N'FN', N'IF', N'TF'))
- drop function [dbo].[f_WorkDayADD]
- GO
- --在指定日期上增加工作天数
- CREATE FUNCTION f_WorkDayADD(
- @date datetime, --基础日期
- @workday int --要增加的工作日数
- )RETURNS datetime
- AS
- BEGIN
- IF @workday>0
- WHILE @workday>0
- SELECT @date=@date+@workday,@workday=count(*)
- FROM tb_Holiday
- WHERE HDate BETWEEN @date AND @date+@workday
- ELSE
- WHILE @workday<0
- SELECT @date=@date+@workday,@workday=-count(*)
- FROM tb_Holiday
- WHERE HDate BETWEEN @date AND @date+@workday
- RETURN(@date)
- END
SQL日期集合-周、月、季度、年等计算以及自定义函数
最新推荐文章于 2024-08-30 11:13:51 发布