【www.bbyears.com--php函数】
代码如下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)) --年末
1)本年初日期
代码如下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
2)上年同期日期 上年同月末天数小于现在天数按最后一天计算
代码如下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
3)取 前 几天 日期
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
4)上月同期日期 上月末天数小于现在天数按最后一天计算
代码如下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
5)上月最后一天日期
代码如下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
6)下面有取得本月初日期 、--本月初日期、本月末日期
一个日期格式sql比较复杂
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) --本月初日期
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)