Question

sql 从身份证中自动获取出生日期,显示格式为XXXX-XX-XX

Answer 1

以oracle为例，通过substr()方法截取年月日，即可。

代码如下：

declare
cardid varchar(20);
birth varchar(20);
begin
/*给cardid赋值一个18位身份证号码*/
birth := substr(cardid,7,4) || '-' || substr(cardid,11,2) || '-' || substr(cardid,13,2);
dbms_output.put_line(birth);
end;

Answer 2

select to_date(substr('123456199010171235',7,8),'yyyy-mm-dd') from dual;

Answer 3

declare @card nvarchar(20),@result nvarchar(20)
set @card='310110190001011239'
set @card='310110801212123'
if len(@card)=18
begin
set @result=substring(@card,7,8)
select substring(@result,1,4)+'-'+substring(@result,5,2)+'-'+substring(@result,7,2)
end
if len(@card)=15
begin
set @result=substring(@card,7,6)
select '19'+substring(@result,1,2)+'-'+substring(@result,3,2)+'-'+substring(@result,5,2)
end

Answer 4

SQL Server,18为身份证

substring(cardid,7,4) + '-' + substring(cardid,11,2) + '-' + substring(cardid,13,2)

Answer 5

很容易的，仔细看看就行了