有以下数据
DM JZRQ SJ
1 2011-12-31 200
2 2011-05-20 100
3 2005-06-30 600
4 1998-01-01 400
5 201201-13 200
................
需要把 JZRQ 是 06-30 ,12-31,03-31 结尾的日期的记录筛掉。 即 查出来的数据,不包括那些日期结尾是06-30,12-31,03-31 的。 有十几年的数据,上面只是 列了一部分。 所以请大家 不要用 not in ('2011-12-31','2005-06-30')
DM JZRQ SJ
1 2011-12-31 200
2 2011-05-20 100
3 2005-06-30 600
4 1998-01-01 400
5 201201-13 200
................
需要把 JZRQ 是 06-30 ,12-31,03-31 结尾的日期的记录筛掉。 即 查出来的数据,不包括那些日期结尾是06-30,12-31,03-31 的。 有十几年的数据,上面只是 列了一部分。 所以请大家 不要用 not in ('2011-12-31','2005-06-30')
Select * From 表名 Where Not (Month(JZRQ)=6 And Day(JZRQ)=30 Or Month(JZRQ)=12 And Day(JZRQ)=31 Or Month(JZRQ)=3 And Day(JZRQ)=31)
create table #date(DM int,JZRQ datetime,qty int)
insert into #date
select '1','2011-12-31',200
union all
select '2','2011-05-20', 100
union all
select '3','2005-06-30', 600
union all
select '4','1998-01-01', 100
union all
select '5','2012-01-13', 200
union all
select '6','2012-03-31', 200
union all
select '7','2012-05-31', 200select * from #date where not(DATEPART(MONTH,JZRQ)=6 and DATEPART(DAY,JZRQ)=30 or
DATEPART(MONTH,JZRQ)=3 and DATEPART(DAY,JZRQ)=31 or DATEPART(MONTH,JZRQ)=12 and DATEPART(DAY,JZRQ)=31 )
2 2011-05-20 00:00:00.000 100
4 1998-01-01 00:00:00.000 100
5 2012-01-13 00:00:00.000 200
7 2012-05-31 00:00:00.000 200
测试数据:
create table #date1(DM int,JZRQ varchar(20),qty int)
insert into #date1
select '1','2011-12-31',200
union all
select '2','2011-05-20', 100
union all
select '3','2005-06-30', 600
union all
select '4','1998-01-01', 100
union all
select '5','2012-01-13', 200
union all
select '6','2012-03-31', 200
union all
select '7','2012-05-31', 200
--如果 JZRQ 为varchar型,则:
select * from #date1
where JZRQ not like '%06-30%'
and jzrq not like '%12-31%'
and jzrq not like '%03-31%'结果:
DM JZRQ qty
----------- -------------------- -----------
2 2011-05-20 100
4 1998-01-01 100
5 2012-01-13 200
7 2012-05-31 200(4 行受影响)若jzrq为datetimeselect * from #date1
where not(DATEPART(MONTH,JZRQ)=6 and DATEPART(DAY,JZRQ)=30 or
DATEPART(MONTH,JZRQ)=3 and DATEPART(DAY,JZRQ)=31 or DATEPART(MONTH,JZRQ)=12 and DATEPART(DAY,JZRQ)=31 )结果:
DM JZRQ qty
----------- -------------------- -----------
2 2011-05-20 100
4 1998-01-01 100
5 2012-01-13 200
7 2012-05-31 200(4 行受影响)