[MSSQL]日付セット-週、月、四半期、年などの計算およびカスタム関数
以下はネット上の収集と自分の仕事の中で書いたいくつかの方法です.
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