SQL怎样从身份证号中提取出生日期_利用SUBSTRING与CAST转换
身份证号第7至14位表示出生日期，格式为YYYYMMDD，需确保字段为字符串类型后截取并显式转换为DATE类型，否则易因类型错误、脏数据或索引失效导致查询失败。
免费影视、动漫、音乐、游戏、小说资源长期稳定更新！ 👉 点此立即查看 👈
身份证号第7到第14位就是出生日期，但得先确认格式
都知道中国大陆18位身份证号的第7到第14位是YYYYMMDD格式，比如19950823。但这里有个关键前提：你得确保数据库里存的这个字段是字符串类型。如果它被存成了数值型（比如BIGINT），那么后续的SUBSTRING操作要么直接报错，要么截取出莫名其妙的结果。常见的坑包括：SUBSTRING返回空值、截取位置发生偏移，或者后续的CAST转换直接提示“invalid input syntax”。
动手之前，建议先做这几步检查：
- 用
SELECT LENGTH(id_card) FROM table_name LIMIT 5快速验证一下，看看是不是全是18位。如果有15位的老号码，需要单独处理（本文聚焦18位新号）。 - 如果发现
id_card字段是数值类型，必须先把它转成字符串。不同数据库语法略有不同：CAST(id_card AS TEXT)或者id_card::TEXT（PostgreSQL），在SQL Server里则是CONVERT(VARCHAR, id_card)。 - MySQL里可以直接用
CAST(id_card AS CHAR)。更稳妥一点的写法是加上LPAD(id_card, 18, '0')来防止前置零丢失——不过话说回来，18位身份证的首位不可能是0，这一步通常可以省略。
SUBSTRING提取后必须显式CAST为DATE类型
用SUBSTRING(id_card, 7, 8)拿到的，本质上还是一个字符串'19950823'。它可不是日期类型，没法直接参与日期计算、排序，或者跟其他DATE字段进行比较。如果直接写WHERE birth_date > '1990-01-01'，数据库会尝试隐式转换，而不同数据库的“脾气”可不一样：MySQL可能容忍，PostgreSQL多半会直接报错，SQL Server则可能按字符串的字典序去比较，结果可想而知。
所以，正确的姿势是显式转换。具体怎么转，还得看数据库：
- PostgreSQL：
CAST(SUBSTRING(id_card, 7, 8) AS DATE)或者简写SUBSTRING(id_card, 7, 8)::DATE。 - SQL Server：
CONVERT(DATE, SUBSTRING(CAST(id_card AS VARCHAR(18)), 7, 8))。这里顺序很重要，得先CAST成字符串再SUBSTRING，因为SUBSTRING函数对纯数字类型可能不买账。 - MySQL：这里有点特殊，得用
STR_TO_DATE(SUBSTRING(id_card, 7, 8), '%Y%m%d')。直接用CAST(... AS DATE)是识别不了'19950823'这种格式的。
遇到NULL或非法值时，SUBSTRING+CAST会中断查询
这才是最让人头疼的地方。只要表里有一条记录的id_card为空、长度不够18位，或者第7到14位里混进了字母之类的非数字字符，整个查询就可能直接报错退出。尤其是在做聚合分析或者创建索引的时候，这种“一颗老鼠屎坏了一锅粥”的情况绝非危言耸听。
怎么办？得加防护判断。比如在PostgreSQL里，可以先用正则清除非数字字符：NULLIF(REGEXP_REPLACE(SUBSTRING(id_card, 7, 8), '\D', '', 'g'), '')，然后再转换。但更治本的建议是，在生产环境里，强烈建议在数据ETL阶段就做好校验和清洗，把异常身份证号单独拎出来处理，而不是等到查询的时候才手忙脚乱地兜底。
如果非要在查询时处理，MySQL里可以写一个相对复杂的判断：IF(LENGTH(id_card)=18 AND id_card REGEXP '^[0-9]{17}[0-9Xx]$', STR_TO_DATE(SUBSTRING(id_card, 7, 8), '%Y%m%d'), NULL)。当然，这会影响性能。
性能影响：SUBSTRING+CAST无法利用索引
另一个容易被忽视的问题是性能。即便你在id_card字段上建了B-Tree索引，像SUBSTRING(id_card, 7, 8)这样的表达式计算是没法走索引的。如果你想查询1990年到1999年出生的人，数据库只能老老实实地做全表扫描，把每条记录都拿出来截取、转换一遍再比较。
对于高频的按出生日期查询的场景，有更优的解决方案：
- 增加冗余字段：最实在的办法，就是在表里直接新增一个
birth_date DATE列。通过触发器或者在应用层写入数据时，就同步解析好并存储进去。一劳永逸。 - 使用函数索引：像PostgreSQL就支持创建函数索引，例如
CREATE INDEX idx_birth ON users ((SUBSTRING(id_card, 7, 8)::DATE))。但要注意，这个索引只对查询条件能完全匹配这个函数表达式的情况有效。 - 避免错误写法：别在WHERE子句里写
CAST(SUBSTRING(id_card, 7, 4) AS INT) BETWEEN 1990 AND 1999。这相当于只截了年份部分来比较，不仅比用完整8位更慢，而且让优化器更加无从下手。
说到底，技术语法本身并不复杂，真正的挑战往往来自于数据本身的不规范。哪怕表中成千上万条记录里，只有那么一两条是15位旧号或者带了空格，就足以让整个批量解析的脚本崩溃。所以，在动手之前，不妨先运行一下这个查询：SELECT id_card FROM t WHERE LENGTH(TRIM(id_card)) NOT IN (15, 18)。看看你的数据到底干不干净，别指望数据库函数能替你搞定所有脏数据。
相关攻略
身份证号第7至14位表示出生日期，格式为YYYYMMDD，需确保字段为字符串类型后截取并显式转换为DATE类型，否则易因类型错误、脏数据或索引失效导致查询失败。 身份证号第7到第14位就是出生日期，但得先确认格式 都知道中国大陆18位身份证号的第7到第14位是YYYYMMDD格式，比如1995082
SQL Server分组字符串拼接：STRING_AGG函数深度解析与避坑指南 SQL Server 2017及以上版本是否支持STRING_AGG函数？ 使用STRING_AGG函数有一个明确的版本限制：它仅在SQL Server 2017及更高版本中作为原生内置函数提供。如果您使用的是SQL S
SQL如何截取字符串的一部分？SUBSTRING函数的实操技巧 SQL里SUBSTRING函数怎么写才不报错？ 想让SUBSTRING函数乖乖听话不报错？第一个要跨过的坎，就是不同数据库在参数顺序和起始位置上的“小脾气”。MySQL和PostgreSQL默认从1开始计数，SQL Server也是这个
热门专题
热门推荐
小米Note 3铃声管理全攻略：从定位到自定义，一步到位 手里拿着小米Note 3，想换个铃声却找不到地方？别急，这事儿其实比想象中简单。系统预置的铃声，都规规矩矩地躺在内部存储的一个特定文件夹里：SDcard MIUI ringtone 。这个目录就像MIUI系统的“声音仓库”，里面分门别类地存放
小米电饭煲重置网络提示失败怎么回事？ 遇到小米电饭煲重置网络总是失败，先别急着怀疑是硬件坏了。这事儿本质上，是设备在配网流程中没能和路由器成功“握手”，建立通信授权。背后的原因，往往出在几个容易被忽略的细节上：比如Wi-Fi频段没选对、密码格式太复杂、App里还残留着旧配置，或者是路由器那边设置了“
按摩椅力度调小后依然有效，关键在于匹配个体身体状态与使用需求 现代中高端按摩椅普遍配备多级力度调节系统，但很多人心里犯嘀咕：力度调小了，是不是就变成隔靴搔痒，没什么实际作用了？ 事实恰恰相反。实测数据显示，轻柔档位（比如30%—50%的输出强度）在缓解日常肩颈僵硬、改善浅层血液循环方面，有着明确的生
米家扫地机器人怎么用手机远程控制 想随时随地指挥家里的扫地机器人干活？这事儿其实很简单。米家APP就是你的万能遥控器，只要几步设置，无论你是在公司、在出差，还是躺在沙发上，都能稳定、便捷地通过手机远程掌控全局。操作逻辑很清晰：在手机上安装好官方米家APP并登录你的小米账号，让扫地机器人连上家里的Wi
PoE交换机好坏，普通测线仪说了不算 想用普通网线测线仪来判断一台PoE交换机的好坏？这个想法很危险。原因很简单：普通测线仪只能干些基础活儿，比如看看网线通不通、线序对不对、有没有短路断路。但对于PoE交换机的核心能力——供电电压是否达标、输出功率稳不稳定、是否兼容最新的IEEE标准、带载后电压会不