declare @date1 varchar(50)
declare @date2 varchar(50)
declare @class04date1 varchar(50)
declare @class04date2 varchar(50)
declare @class56date1 varchar(50)
declare @class56date2 varchar(50)
declare @class712date1 varchar(50)
declare @class712date2 varchar(50)
declare @class1324date1 varchar(50)
declare @class1324date2 varchar(50)set @date1='2009-01-01'
set @date2='2009-09-30'set @class04date1=convert(varchar(8),dateadd(month,-3,@date2),120)+'01'
set @class04date2=convert(varchar(8),dateadd(month,-0,@date2),120)+'30'
set @class56date1=convert(varchar(8),dateadd(month,-5,@date2),120)+'01'
set @class56date2=convert(varchar(8),dateadd(month,-4,@date2),120)+'30'
set @class712date1=convert(varchar(8),dateadd(month,-11,@date2),120)+'01'
set @class712date2=convert(varchar(8),dateadd(month,-6,@date2),120)+'30'
set @class1324date1=convert(varchar(8),dateadd(month,-23,@date2),120)+'01'
set @class1324date2=convert(varchar(8),dateadd(month,-12,@date2),120)+'30'得到的第二个日期,如class04date2,class56date2,class712date2,class1324date2带的都是30,现在要判断如果是1月份,则是31,2月份则是28,有可能是29,随年份而定,3月份又是31.
总之,如果前提是@date2='2009-09-30'最后的结果就要是@class04date2='2009-09-30',@class56date2='2009-05-31',@class712date2='2009-03-31',@class1324date2='2008-09-30',
declare @date2 varchar(50)
declare @class04date1 varchar(50)
declare @class04date2 varchar(50)
declare @class56date1 varchar(50)
declare @class56date2 varchar(50)
declare @class712date1 varchar(50)
declare @class712date2 varchar(50)
declare @class1324date1 varchar(50)
declare @class1324date2 varchar(50)set @date1='2009-01-01'
set @date2='2009-09-30'set @class04date1=convert(varchar(8),dateadd(month,-3,@date2),120)+'01'
set @class04date2=convert(varchar(8),dateadd(month,-0,@date2),120)+'30'
set @class56date1=convert(varchar(8),dateadd(month,-5,@date2),120)+'01'
set @class56date2=convert(varchar(8),dateadd(month,-4,@date2),120)+'30'
set @class712date1=convert(varchar(8),dateadd(month,-11,@date2),120)+'01'
set @class712date2=convert(varchar(8),dateadd(month,-6,@date2),120)+'30'
set @class1324date1=convert(varchar(8),dateadd(month,-23,@date2),120)+'01'
set @class1324date2=convert(varchar(8),dateadd(month,-12,@date2),120)+'30'得到的第二个日期,如class04date2,class56date2,class712date2,class1324date2带的都是30,现在要判断如果是1月份,则是31,2月份则是28,有可能是29,随年份而定,3月份又是31.
总之,如果前提是@date2='2009-09-30'最后的结果就要是@class04date2='2009-09-30',@class56date2='2009-05-31',@class712date2='2009-03-31',@class1324date2='2008-09-30',
declare @date2 varchar(50)
declare @class04date1 varchar(50)
declare @class04date2 varchar(50)
declare @class56date1 varchar(50)
declare @class56date2 varchar(50)
declare @class712date1 varchar(50)
declare @class712date2 varchar(50)
declare @class1324date1 varchar(50)
declare @class1324date2 varchar(50) set @date1='2009-01-01'
set @date2='2009-09-30' set @class04date1=convert(varchar(8),dateadd(month,-3,@date2),120)+'01'
set @class04date2=datediff(day,-1,convert(varchar(8),dateadd(month,1,@date2),120)+'01')
set @class56date1=convert(varchar(8),dateadd(month,-5,@date2),120)+'01'
set @class56date2=datediff(day,-1,convert(varchar(8),dateadd(month,-3,@date2),120)+'01')
set @class712date1=convert(varchar(8),dateadd(month,-11,@date2),120)+'01'
set @class712date2=datediff(day,-1,convert(varchar(8),dateadd(month,-5,@date2),120)+'01')
set @class1324date1=convert(varchar(8),dateadd(month,-23,@date2),120)+'01'
set @class1324date2=datediff(DAY,-1,convert(varchar(8),dateadd(month,-12,@date2),120)+'01')
WHEN DATEPART(MM,[TIME])=2 THEN..........?
declare @date2 varchar(50)
declare @class04date1 varchar(50)
declare @class04date2 varchar(50)
declare @class56date1 varchar(50)
declare @class56date2 varchar(50)
declare @class712date1 varchar(50)
declare @class712date2 varchar(50)
declare @class1324date1 varchar(50)
declare @class1324date2 varchar(50) set @date1='2009-01-01'
set @date2='2009-09-30' set @class04date1=convert(varchar(8),dateadd(month,-3,@date2),120)+'01'
set @class04date2=datediff(day,-1,cast(convert(varchar(8),dateadd(month,1,@date2),120)+'01' as datetime))
set @class56date1=convert(varchar(8),dateadd(month,-5,@date2),120)+'01'
set @class56date2=datediff(day,-1,cast(convert(varchar(8),dateadd(month,-3,@date2),120)+'01'as datetime))
set @class712date1=convert(varchar(8),dateadd(month,-11,@date2),120)+'01'
set @class712date2=datediff(day,-1,cast(convert(varchar(8),dateadd(month,-5,@date2),120)+'01'as datetime))
set @class1324date1=convert(varchar(8),dateadd(month,-23,@date2),120)+'01'
set @class1324date2=datediff(DAY,-1,cast(convert(varchar(8),dateadd(month,-12,@date2),120)+'01'as datetime))
declare @date1 varchar(50)
declare @date2 varchar(50)
declare @class04date1 varchar(50)
declare @class04date2 varchar(50)
declare @class56date1 varchar(50)
declare @class56date2 varchar(50)
declare @class712date1 varchar(50)
declare @class712date2 varchar(50)
declare @class1324date1 varchar(50)
declare @class1324date2 varchar(50)set @date1='2009-01-01'
set @date2='2009-09-30'set @class04date1=convert(varchar(8),dateadd(month,-3,@date2),120)+'01'
set @class04date2=convert(varchar(10),dateadd(day,-1,dateadd(month,1,convert(datetime,@class04date1))),120)
set @class56date1=convert(varchar(8),dateadd(month,-5,@date2),120)+'01'
set @class56date2=convert(varchar(10),dateadd(day,-1,dateadd(month,1,convert(datetime,@class04date1))),120)
set @class712date1=convert(varchar(8),dateadd(month,-11,@date2),120)+'01'
set @class712date2=convert(varchar(10),dateadd(day,-1,dateadd(month,1,convert(datetime,@class04date1))),120)
set @class1324date1=convert(varchar(8),dateadd(month,-23,@date2),120)+'01'
set @class1324date2=convert(varchar(10),dateadd(day,-1,dateadd(month,1,convert(datetime,@class04date1))),120)SELECT @class04date1,@class04date2,@class56date1,@class56date2
,@class712date1,@class712date2,@class1324date1,@class1324date2--2009-06-01 2009-06-30 2009-04-01 2009-06-30 2008-10-01 2009-06-30 2007-10-01 2009-06-30
declare @date2 varchar(50)
declare @class04date1 varchar(50)
declare @class04date2 varchar(50)
declare @class56date1 varchar(50)
declare @class56date2 varchar(50)
declare @class712date1 varchar(50)
declare @class712date2 varchar(50)
declare @class1324date1 varchar(50)
declare @class1324date2 varchar(50) set @date1='2009-01-01'
set @date2='2009-09-30' set @class04date1=convert(varchar(8),dateadd(month,-3,@date2),120)+'01'
set @class04date2=convert(varchar(10),dateadd(day,-1,cast(convert(varchar(8),dateadd(month,1,@date2),120)+'01'as datetime)),120)
set @class56date1=convert(varchar(8),dateadd(month,-5,@date2),120)+'01'
set @class56date2=convert(varchar(10),dateadd(day,-1,cast(convert(varchar(8),dateadd(month,-3,@date2),120)+'01'as datetime)),120)
set @class712date1=convert(varchar(8),dateadd(month,-11,@date2),120)+'01'
set @class712date2=convert(varchar(10),dateadd(day,-1,cast(convert(varchar(8),dateadd(month,-5,@date2),120)+'01'as datetime)) ,120)
set @class1324date1=convert(varchar(8),dateadd(month,-23,@date2),120)+'01'
set @class1324date2=convert(varchar(10),dateadd(DAY,-1,cast(convert(varchar(8),dateadd(month,-12,@date2),120)+'01'as datetime)) ,120)
SELECT @class04date1,@class04date2,@class56date1,@class56date2
,@class712date1,@class712date2,@class1324date1,@class1324date2
/*
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
2009-06-01 2009-09-30 2009-04-01 2009-05-31 2008-10-01 2009-03-31 2007-10-01 2008-08-31(1 行受影响)
*/
declare @date2 varchar(50)
declare @class04date1 varchar(50)
declare @class04date2 varchar(50)
declare @class56date1 varchar(50)
declare @class56date2 varchar(50)
declare @class712date1 varchar(50)
declare @class712date2 varchar(50)
declare @class1324date1 varchar(50)
declare @class1324date2 varchar(50) set @date1='2009-01-01'
set @date2='2009-09-30' set @class04date1=convert(varchar(8),dateadd(month,-3,@date2),120)+'01'
set @class04date2=@date2
set @class56date1=convert(varchar(10),dateadd(month,-5,@date2),120)
set @class56date2=convert(varchar(10),dateadd(day,1,DATEADD(month,-4,@date2)),120)
set @class712date1=convert(varchar(8),dateadd(month,-11,@date2),120)+'01'
set @class712date2=convert(varchar(10),dateadd(day,1,DATEADD(month,-6,@date2)),120)
set @class1324date1=convert(varchar(8),dateadd(month,-23,@date2),120)+'01'
set @class1324date2=convert(varchar(10),dateadd(day,0,DATEADD(month,-3,@date2)),120)
select @class04date1 class04date1,@class04date2 as class04date2,@class56date1 class56date1,@class56date2 class56date2,@class712date1 class712date1,
@class712date2 class712date2,@class1324date1 class1324date1,@class1324date2 class1324date2
/*
class04date1 class04date2 class56date1 class56date2 class712date1 class712date2 class1324date1 class1324date2
2009-06-01 2009-09-30 2009-04-30 2009-05-31 2008-10-01 2009-03-31 2007-10-01 2009-06-30
*/
declare @date2 varchar(50)
declare @class04date1 varchar(50)
declare @class04date2 varchar(50)
declare @class56date1 varchar(50)
declare @class56date2 varchar(50)
declare @class712date1 varchar(50)
declare @class712date2 varchar(50)
declare @class1324date1 varchar(50)
declare @class1324date2 varchar(50) set @date1='2009-01-01'
set @date2='2009-09-30' set @class04date1=convert(varchar(8),dateadd(month,-3,@date2),120)+'01'
set @class04date2=@date2
set @class56date1=convert(varchar(10),dateadd(month,-5,@date2),120)
set @class56date2=convert(varchar(10),dateadd(day,1,DATEADD(month,-4,@date2)),120)
set @class712date1=convert(varchar(8),dateadd(month,-11,@date2),120)+'01'
set @class712date2=convert(varchar(10),dateadd(day,1,DATEADD(month,-6,@date2)),120)
set @class1324date1=convert(varchar(8),dateadd(month,-23,@date2),120)+'01'
set @class1324date2=convert(varchar(10),dateadd(day,0,DATEADD(month,-12,@date2)),120)
select @class04date1 class04date1,@class04date2 as class04date2,@class56date1
class56date1,@class56date2 class56date2,@class712date1 class712date1,
@class712date2 class712date2,@class1324date1 class1324date1,
@class1324date2 class1324date2/*
class04date1 class04date2 class56date1 class56date2 class712date1 class712date2 class1324date1 class1324date2
2009-06-01 2009-09-30 2009-04-30 2009-05-31 2008-10-01 2009-03-31 2007-10-01 2008-09-30
*/不好意思,刚才输错了