比如表如下:Date1 Date2
2010-03-12 2010-05-30
2009-10-10 2009-12-05
我想得到的结果
Date
2010-03
2010-04
2009-10
2009-11也就是对比Date2与Date1相差的月份,把相差的结果按月份一条一条的查询出来。
2010-03-12 2010-05-30
2009-10-10 2009-12-05
我想得到的结果
Date
2010-03
2010-04
2009-10
2009-11也就是对比Date2与Date1相差的月份,把相差的结果按月份一条一条的查询出来。
from [Table] A,master..spt_values B
where B.number<13 and B.type='p' and dateadd(day,number,Date1)<cast(date2 as datetime)
insert into tb values('2010-03-12' ,'2010-05-30')
insert into tb values('2009-10-10' ,'2009-12-05')
goselect n.* , convert(varchar(7),dateadd(mm,m.px,Date1),120) dt
from tb n,(select px = (select count(1) from sysobjects where id < t.id) from sysobjects t) m
where convert(varchar(7),dateadd(mm,m.px,Date1),120) < convert(varchar(7),Date2,120)
order by n.date1
drop table tb/*
Date1 Date2 dt
------------------------------------------------------ ------------------------------------------------------ -------
2009-10-10 00:00:00.000 2009-12-05 00:00:00.000 2009-10
2009-10-10 00:00:00.000 2009-12-05 00:00:00.000 2009-11
2010-03-12 00:00:00.000 2010-05-30 00:00:00.000 2010-03
2010-03-12 00:00:00.000 2010-05-30 00:00:00.000 2010-04(所影响的行数为 4 行)
*/
insert @a select '2010-03-12' ,'2010-05-30'
union all select '2009-10-10' ,'2009-12-05'SELECT DISTINCT convert(varchar(7), dateadd(MONTH,number,Date1),120)
from @a A,master..spt_values B
where B.number<13 and B.type='p'
and convert(varchar(7),dateadd(month,number,Date1),120)<convert(varchar(7),cast(date2 as datetime),120)--result
/*-------
2009-10
2009-11
2010-03
2010-04(所影响的行数为 4 行)*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-30 10:54:45
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Date1] datetime,[Date2] datetime)
insert [tb]
select '2010-03-12','2010-05-30' union all
select '2009-10-10','2009-12-05'
--------------开始查询--------------------------
select
convert(varchar(7),dateadd(mm,number,Date1),120)
from
tb A,master..spt_values B
where
B.number<13 and B.type='p'
and
convert(varchar(7),dateadd(month,number,Date1),120)<convert(varchar(7),cast(date2 as datetime),120)
----------------结果----------------------------
/* 2010-03
2010-04
2009-10
2009-11(4 行受影响)
*/