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)