有个问题想问下:
我想每个月导出当月的报表,比如2009.2.1--2009.2.28的报表,2009.3.1--2009.3.31的报表,2009.4.1--2009.4.30的报表...请问因为每个月的天数不一样,我应该怎么设置当月的所符合交易记录的筛选条件呢?
我想每个月导出当月的报表,比如2009.2.1--2009.2.28的报表,2009.3.1--2009.3.31的报表,2009.4.1--2009.4.30的报表...请问因为每个月的天数不一样,我应该怎么设置当月的所符合交易记录的筛选条件呢?
create table test_DateTime(dTime dateTime)
insert into test_DateTime
select '2009-02-01' union all
select '2009-02-06' union all
select '2009-02-28' union all
select '2009-03-05'
select * from test_DateTime where datepart(mm,dTime) = 2
2. datepart(mm,col1) = ....
insert into test_DateTime
select '2009-02-01' union all
select '2009-02-06' union all
select '2009-02-28' union all
select '2009-03-05'
select * from test_DateTime where month(convert(varchar(10),dTime,120))=2(所影响的行数为 4 行)dTime
------------------------------------------------------
2009-02-01 00:00:00.000
2009-02-06 00:00:00.000
2009-02-28 00:00:00.000(所影响的行数为 3 行)
declare @table table (dtime varchar(10))
insert into @table
select '2007-12-01' union all
select '2007-02-09' union all
select '2008-03-12' union all
select '2008-09-08' union all
select '2009-01-12' union all
select '2009-01-13' union all
select '2009-02-12' union all
select '2009-02-19' union all
select '2009-03-11'declare @GetMonth varchar(10)
set @GetMonth='2009-02'select dtime from @table where convert(varchar(20),dtime,120) like '%'+@GetMonth+'%'/*
dtime
----------
2009-02-12
2009-02-19
*/
set @dtarg = '2009-05-27' -- 指定月份内的任意日期declare @dtBgn datetime, @dtEnd datetime
select @dtBgn = dateadd(month,datediff(month,0,@dtarg),0) -- 月初第一天零时
, @dtEnd = dateadd(ms,-3,dateadd(month,datediff(month,0,@dtarg)+1,0)) -- 月末时刻declare @dtRange varchar(32) -- 报告所用的格式化字符串:日期范围
select @dtRange =
convert(varchar(10),@dtBgn,102)+'--'+
convert(varchar(10),@dtEnd,102)select ...
from ...
where dt between @dtBgn and @dtEnd