@d datetimeSELECT
ISNULL ( SUM(CASE WHEN qfrq BETWEEN CONVERT(varchar(8),@d,120)+'01'
AND @d
THEN sjfl ELSE 0 END),0) AS 本月方量
这个是查询自然月的 可现在需要25-25的结账日
比如:2012-08-21 那应该查询 2012-07-26-------201208-21的数据
2012-07-25 那应该查询 2012-06-26------2012-07-25
如何做?
ISNULL ( SUM(CASE WHEN qfrq BETWEEN CONVERT(varchar(8),@d,120)+'01'
AND @d
THEN sjfl ELSE 0 END),0) AS 本月方量
这个是查询自然月的 可现在需要25-25的结账日
比如:2012-08-21 那应该查询 2012-07-26-------201208-21的数据
2012-07-25 那应该查询 2012-06-26------2012-07-25
如何做?
->?
declare @dt datetime
set @dt='2012-08-21'
select qfrq between convert(varchar(7),dateadd(month,-1,@dt)),120)+'-25' and @dt from tab
declare @d0 datetime
set @d0='1900-1-25'
declare @time datetime
set @time='2011-1-1'
--用偏移量计算
select case when DAY(@time)>=25 then DATEADD(MONTH,DATEDIFF(month,@d0,@time),@d0) else DATEADD(MONTH,DATEDIFF(month,@d0,@time)-1,@d0) end
declare @date datetime
set @dt='2012-07-25'
select convert(varchar(7),dateadd(month,-1,'2012-07-25'),120)+'-25' /*2012-06-25*/
*/
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 10000;
SET @rc = 1;INSERT INTO Nums VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
SET @rc = @rc * 2;
ENDINSERT INTO dbo.Nums
SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;
GO/*函数用于返回时间的月份序列,类似方法可以产生日序列,周序列 等等
参数:@s 开始时间
@e 结束时间
*磊仔 2012年5月29日创建
*/
Create function dbo.fn_DayList(@s as datetime,@e as datetime) returns table
as
return
with CET1 AS
( SELECT cast(convert(varchar(6),dateadd(mm, n - 1,@s) ,112) as int) as [Month],--月序列
dateadd(dd,25,dateadd(mm,-1,dateadd(d,datediff(d,0,dateadd(mm, n - 1,@s)),0) - DATEPART(dd, dateadd(mm, n - 1,@s))+1))as StartT,
dateadd(dd,25,dateadd(d,datediff(d,0,dateadd(mm, n - 1,@s)),0) - DATEPART(dd, dateadd(mm, n - 1,@s))+1) as EndT
FROM dbo.Nums
WHERE n <= DATEDIFF(mm, @s, @e)
)SELECT * FROM CET1
GO--创建完以上表值函数后就可轻松调用,且性能极佳declare @time datetime
set @time='2011-7-25'
select StartT, @Time as NowTime
from dbo.fn_DayList('19000101','20500101')
where StartT <= @Time and EndT > @Time
StartT NowTime
----------------------- -----------------------
2011-06-26 00:00:00.000 2011-07-25 00:00:00.000(1 行受影响)