变量@date_m nvarchar(7) 表示年份及月份
变量@date_d nvarchar(2) 表示日,可能是一个月的第一天,即01日,又或25日,又或最后一天31日 。当我设置set date_m='2011-09' set date_d='01'时得到:上月的日期为:2011-08-31 本月日期为:2011-09-01
当我设置set date_m='2011-09' set date_d='25'时得到:上月的日期为:2011-08-25 本月日期为:2011-09-25
当我设置set date_m='2011-09' set date_d='31'时得到:上月的日期为:2011-08-31 本月日期为:2011-09-30
当我设置set date_m='2011-02' set date_d='31'时得到:上月的日期为:2011-01-31 本月日期为:2011-02-28也就是由变量@date_m nvarchar(7) @date_d nvarchar(2)
得到上月日期@date_1 datetime 本月日期@date_2 datetime
变量@date_d nvarchar(2) 表示日,可能是一个月的第一天,即01日,又或25日,又或最后一天31日 。当我设置set date_m='2011-09' set date_d='01'时得到:上月的日期为:2011-08-31 本月日期为:2011-09-01
当我设置set date_m='2011-09' set date_d='25'时得到:上月的日期为:2011-08-25 本月日期为:2011-09-25
当我设置set date_m='2011-09' set date_d='31'时得到:上月的日期为:2011-08-31 本月日期为:2011-09-30
当我设置set date_m='2011-02' set date_d='31'时得到:上月的日期为:2011-01-31 本月日期为:2011-02-28也就是由变量@date_m nvarchar(7) @date_d nvarchar(2)
得到上月日期@date_1 datetime 本月日期@date_2 datetime
insert into tb select '2011-09','01'
insert into tb select '2011-09','25'
insert into tb select '2011-09','31'
insert into tb select '2011-02','31'
go
select *,
case when d='01' then DATEADD(d,-1,m+'-'+d)
when d=25 then DATEADD(m,-1,m+'-'+d)
else DATEADD(d,-1,m+'-01')
end as dt
from tb
/*
m d dt
------- ---- -----------------------
2011-09 01 2011-08-31 00:00:00.000
2011-09 25 2011-08-25 00:00:00.000
2011-09 31 2011-08-31 00:00:00.000
2011-02 31 2011-01-31 00:00:00.000(4 行受影响)*/
go
drop table tb
insert into tb select '2011-09','01'
insert into tb select '2011-09','25'
insert into tb select '2011-09','31'
insert into tb select '2011-02','31'
go
select *,
case when d='01' then DATEADD(d,-1,m+'-'+d)
when d=25 then DATEADD(m,-1,m+'-'+d)
else DATEADD(d,-1,m+'-01')
end as dt1,
case when d='01' or d='25' then m+'-'+d
else dateadd(d,-1,DATEADD(m,1,m+'-01'))
end as dt2
from tb
/*
m d dt1 dt2
------- ---- ----------------------- -----------------------
2011-09 01 2011-08-31 00:00:00.000 2011-09-01 00:00:00.000
2011-09 25 2011-08-25 00:00:00.000 2011-09-25 00:00:00.000
2011-09 31 2011-08-31 00:00:00.000 2011-09-30 00:00:00.000
2011-02 31 2011-01-31 00:00:00.000 2011-02-28 00:00:00.000(4 行受影响)
*/
go
drop table tb
drop function fn_Date
go
Create function fn_Date(
@date_m nvarchar(7),
@date_d nvarchar(2)
)
returns datetime
as
begin
declare @dt datetime,@dt2 datetime
select @dt=@date_m+'-01',@dt2=dateadd(m,-1,@dt)
if day(dateadd(m,1,@dt)-1)<=cast(@date_d as int) or @date_d='01'
return @dt-1
return (dateadd(m,1,@dt)+cast(@date_d as int)-1)end
go
select dbo.fn_Date('2011-09','01')select dbo.fn_Date('2011-09','25')select dbo.fn_Date('2011-09','31')select dbo.fn_Date('2011-02','31')
当set date_m='2011-02' set date_d='31'时得到:上月的日期为:2011-01-31 本月日期:2011-02-28即当set date_m='2011-02' set date_d='31'时
得到:date1='2011-01-31' date2='2011-02-28'我要的结果是data1及date2
declare @tb table (date_m nvarchar(10),date_d nvarchar(2))
insert into @tb select '2011-09','01'
insert into @tb select '2011-09','25'
insert into @tb select '2011-09','31'
insert into @tb select '2011-02','31'select
case when day(dateadd(m,1,cast(date_m+'-01' as datetime))-1)<=cast(date_d as int) or date_d='01' then cast(date_m+'-01' as datetime)-1
else (dateadd(m,-1,date_m+'-01')+cast(date_d as int)-1) end as dt1,
case when day(dateadd(m,1,cast(date_m+'-01' as datetime))-1)<=cast(date_d as int) then cast(date_m+'-01' as datetime)-1 else date_m+'-'+date_d end as dt2
from
@tb
/*
dt1 dt2
2011-08-31 00:00:00.000 2011-09-01 00:00:00.000
2011-08-25 00:00:00.000 2011-09-25 00:00:00.000
2011-08-31 00:00:00.000 2011-08-31 00:00:00.000
2011-01-31 00:00:00.000 2011-01-31 00:00:00.000
*/