现在sql数据库用有一列,现在想用一个sql语句就能筛选出 符合生日在??????????1947-7-1到1952-6-30之间的身份证号列表信息
如：身份证号
372524198203033778?
370206195609141619?
370221193501010048?
??????????????????结果????是? ?????????出生?????日期在?????询??1947-7-1到1952-6-30的身份证号筛选列表，跪求 sql语句???????????????????????????????????????????????????????????????????????????
身份证得考虑： 15位和18位
------解决方案--------------------
- SQL code
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')BEGIN DROP TABLE tbaENDGOCREATE TABLE tba( col1 VARCHAR(18))GOINSERT INTO tbaSELECT '210603198502121018' UNIONSELECT '210603850212101'SELECT * FROM tbaWHERE CONVERT(VARCHAR(8),'1947-07-01',112) <= CASE LEN(col1) WHEN 18 THEN SUBSTRING(col1,7,8) WHEN 15 THEN CONVERT(VARCHAR(8),CAST(SUBSTRING(col1,7,6) AS DATETIME),112) END AND CONVERT(VARCHAR(8),'1987-6-30',112) >= CASE LEN(col1) WHEN 18 THEN SUBSTRING(col1,7,8) WHEN 15 THEN CONVERT(VARCHAR(8),CAST(SUBSTRING(col1,7,6) AS DATETIME),112) END
------解决方案--------------------
- SQL code
create table fe(身份证号 varchar(18))insert into feselect '372524198203033778' union allselect '370206195609141619' union allselect '370221193501010048' union allselect '370221194801010048' union allselect '370221490102004'select *from fewhere cast(case len(身份证号) when 18 then substring(身份证号,7,8) when 15 then '19'+substring(身份证号,7,6) end as date)between '1947-07-01' and '1952-06-30'/*身份证号------------------370221194801010048370221490102004(2 row(s) affected)*/
------解决方案--------------------
- SQL code
select case length(id_card) when 15 then cast('19'+substring(ID_card,7,2)+'-'+substring(ID_card,11,2)+'-'+substring(ID_card,13,2) as datetime) when 18 then cast(substring(ID_card,7,4)+'-'+substring(ID_card,11,2)+'-'+substring(ID_card,13,2) as datetime) AS birthdate where birthdate between '1947-7-1' and '1952-6-30'