sql中日期日期周末月末函数

select Dateadd(wk,datediff(wk,0,getdate()),0) --周一
select Dateadd(wk,datediff(wk,0,getdate()),6) --周日

select Dateadd(mm,datediff(mm,0,getdate()),0) --月初
select Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) --月末

select Dateadd(yy,datediff(yy,0,getdate()),0) --年初
select Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) --年末

CREATE FUNCTION GetYearFirstDay(@yourDate varchar(10)) --本年初日期
RETURNS varchar(10)
AS
BEGIN
declare @getDate datetime,@getDateYear int,@returnDate datetime
set @getDate=cast(@yourDate as datetime)
set @getDateYear=year(@getDate) --传入日期年份
set @returnDate=cast(str(@getDateYear)+'-'+'01'+'-01' as datetime) --本年初日期
return convert(varchar(10),@returnDate,120)
END

CREATE FUNCTION GetPreYearSameDay(@yourDate varchar(10)) --上年同期日期 上年同月末天数小于现在天数按最后一天计算
RETURNS varchar(10)
AS
BEGIN
declare @getDate datetime,@getDateYear int,@getDateMonth int,@getDateDay int,@returnDate datetime,@returnDateYear int,@returnDateMonth int,@returnDateDay int
set @getDate=cast(@yourDate as datetime)
set @getDateYear=year(@getDate) --传入日期年份
set @getDateMonth=month(@getDate) --传入日期月份
set @getDateDay=day(@getDate) --传入日期天

set @returnDate=DateAdd(m,1,@getDate) --取得下月日期

set @returnDate=cast(str(year(@returnDate)-1)+'-'+str(month(@returnDate))+'-'+'01' as datetime)-1 --取得去年同月的最后一天日期

set @returnDateYear=year(@returnDate) --去年同月 年份
set @returnDateMonth=month(@returnDate) --去年同月 月份
set @returnDateDay=day(@returnDate) --去年同月最后一天 天

if @getDateDay>@returnDateDay --传入日期天数大于去年同月末天数 返回去年同月最后一天
begin
set @returnDate=@returnDate
end
else
begin
set @returnDate=cast(str(@returnDateYear)+'-'+str(@returnDateMonth)+'-'+str(@getDateDay) as datetime)
end
return convert(varchar(10),@returnDate,120)
END

CREATE FUNCTION GetPreSomeDate(@yourDate varchar(10),@PreDays int) --取 前 几天 日期
RETURNS varchar(10)
AS

BEGIN
declare @getDate datetime,@returnDate datetime

set @getDate=cast(@yourDate as datetime)
set @returnDate=@getDate-@PreDays
return convert(varchar(10),@returnDate,120)
END

CREATE FUNCTION GetPreMonthSameDay(@yourDate varchar(10)) --上月同期日期 上月末天数小于现在天数按最后一天计算
RETURNS varchar(10)
AS

BEGIN
declare @getDate datetime,@getDateYear int,@getDateMonth int,@getDateDay int,@returnDate datetime,@returnDateYear int,@returnDateMonth int,@returnDateDay int
set @getDate=cast(@yourDate as datetime)
set @getDateYear=year(@getDate) --传入日期年份
set @getDateMonth=month(@getDate) --传入日期月份
set @getDateDay=day(@getDate) --传入日期天

set @returnDate=cast(str(@getDateYear)+'-'+str(@getDateMonth)+'-01' as datetime)-1 --上月最后一天
set @returnDateYear=year(@returnDate) --上月最后一天 年份
set @returnDateMonth=month(@returnDate) --上月最后一天 月份
set @returnDateDay=day(@returnDate) --上月最后一天 天

if @getDateDay>@returnDateDay --传入日期天数大于上月末天数 返回上月最后一天
begin
set @returnDate=@returnDate
end
else
begin
set @returnDate=cast(str(@returnDateYear)+'-'+str(@returnDateMonth)+'-'+str(@getDateDay) as datetime)
end
return convert(varchar(10),@returnDate,120)
END

CREATE FUNCTION GetPreMonthLastDay(@yourDate varchar(10)) --上月最后一天日期
RETURNS varchar(10)
AS

BEGIN
declare @getDate datetime,@getDateYear int,@getDateMonth int,@returnDate datetime
set @getDate=cast(@yourDate as datetime)
set @getDateYear=year(@getDate)
set @getDateMonth=month(@getDate)
set @returnDate=cast(str(@getDateYear)+'-'+str(@getDateMonth)+'-01' as datetime)-1
return convert(varchar(10),@returnDate,120)
END

BEGIN
declare @getDate datetime,@getDateYear int,@getDateMonth int,@returnDate datetime
set @getDate=cast(@yourDate as datetime)
set @getDateYear=year(@getDate) --传入日期年份
set @getDateMonth=month(@getDate) --传入日期月份
set @returnDate=cast(str(@getDateYear)+'-'+str(@getDateMonth)+'-01' as datetime) --本月初日期
return convert(varchar(10),@returnDate,120)
END

CREATE FUNCTION GetMonthLastDay(@Date datetime)
RETURNS datetime
AS
----本月末日期
BEGIN
declare @getDate datetime,@getDateYear int,@getDateMonth int,@returnDate datetime
set @getDate=DATEADD(mm,1,@date)
set @getDateYear=year(@getDate)
set @getDateMonth=month(@getDate)
set @returnDate=cast(str(@getDateYear)+'-'+str(@getDateMonth)+'-01' as datetime)-1
return @returnDate
END

select '100' as region,
convert(varchar(30),getdate(),100) as style
union
select '101', convert(varchar(30),getdate(),101)
union
select '102',convert(varchar(30),getdate(),102)
union
select '103',convert(varchar(30),getdate(),103)
union
select '104',convert(varchar(30),getdate(),104)
union
select '105',convert(varchar(30),getdate(),105)
union
select '106',convert(varchar(30),getdate(),106)
union
select '107',convert(varchar(30),getdate(),107)
union
select '108',convert(varchar(30),getdate(),108)
union
select '109',convert(varchar(30),getdate(),109)
union
select '110',convert(varchar(30),getdate(),110)
union
select '111',convert(varchar(30),getdate(),111)
union
select '112',convert(varchar(30),getdate(),112)
union
select '113',convert(varchar(30),getdate(),113)
union
select '11',convert(varchar(30),getdate(),114)