有以下数据
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')
------解决方案--------------------
如果你的JZRQ数据类型是Datetime
- SQL code
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)
------解决方案--------------------
- SQL code
create table #date(DM int,JZRQ datetime,qty int)insert into #dateselect '1','2011-12-31',200union allselect '2','2011-05-20', 100union allselect '3','2005-06-30', 600union allselect '4','1998-01-01', 100union allselect '5','2012-01-13', 200union allselect '6','2012-03-31', 200union allselect '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 1004 1998-01-01 00:00:00.000 1005 2012-01-13 00:00:00.000 2007 2012-05-31 00:00:00.000 200
------解决方案--------------------
- SQL code
测试数据：create table #date1(DM int,JZRQ varchar(20),qty int)insert into #date1select '1','2011-12-31',200union allselect '2','2011-05-20', 100union allselect '3','2005-06-30', 600union allselect '4','1998-01-01', 100union allselect '5','2012-01-13', 200union allselect '6','2012-03-31', 200union allselect '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 1004 1998-01-01 1005 2012-01-13 2007 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 1004 1998-01-01 1005 2012-01-13 2007 2012-05-31 200(4 行受影响)