SQL 现在是按天查询怎么修改成按日期查询呢！如查询 2013-01-01 到 2014-01-01 中间的数据
现在的代码如下
现的代码是查询 30 以上 90一下 为来店消费的会员
select a.gongsimc,a.card_kehu_no as kehu_no,a.card_no,a.card_kehu_mc as kehu_mc,a.card_kehu_shouji as
kehu_dh,a.che_no,a.card_kind,a.card_enddate,lastdate,datediff(day,isnull(lastdate,getdate()-365),getdate()) days
from ( select zhifu_card_no,max(lastdate) as lastdate from ( select zhifu_card_no ,xche_jsrq lastdate from work_pz_sj
union select zhifu_card_no,xc_rq lastdate from work_xiche_pz_sj union select zhifu_card_no,xiao_rq lastdate from xiaosh_pz_sj
union select card_no as zhifu_card_no,xche_jsrq lastdate from work_pz_sj
union select card_no as zhifu_card_no,xc_rq lastdate from work_xiche_pz_sj
union select card_no as zhifu_card_no,xiao_rq lastdate from xiaosh_pz_sj) aa group by zhifu_card_no)
b left join card a on b.zhifu_card_no=a.card_no
where a.card_no is not null and datediff(day,isnull(lastdate,getdate()-365),getdate())>=30 and datediff(day,isnull(lastdate,getdate()-365),getdate())<=90 and a.GongSiNo = '02'
------解决方案--------------------
这样？
SELECT a.gongsimc ,
a.card_kehu_no AS kehu_no ,
a.card_no ,
a.card_kehu_mc AS kehu_mc ,
a.card_kehu_shouji AS kehu_dh ,
a.che_no ,
a.card_kind ,
a.card_enddate ,
lastdate ,
DATEDIFF(day, ISNULL(lastdate, GETDATE() - 365), GETDATE()) days
FROM ( SELECT zhifu_card_no ,
MAX(lastdate) AS lastdate
FROM ( SELECT zhifu_card_no ,
xche_jsrq lastdate
FROM work_pz_sj
UNION
SELECT zhifu_card_no ,
xc_rq lastdate
FROM work_xiche_pz_sj
UNION
SELECT zhifu_card_no ,
xiao_rq lastdate
FROM xiaosh_pz_sj
UNION
SELECT card_no AS zhifu_card_no ,
xche_jsrq lastdate
FROM work_pz_sj
UNION
SELECT card_no AS zhifu_card_no ,