从请教Sql语句从18位身份证号生成出生日期

Question

mssql2008express中
update baseinfo set chushengriqi=
convert(datetime,
''''+
+substring(CONVERT(char(4),shenfenzhenghao),7,4)+'-'
+substring(CONVERT(char(2),shenfenzhenghao),11,2)+'-'
+substring(CONVERT(char(2),shenfenzhenghao),13,2)+''''
)
where ShenFenZhengHao is not null
and len(ShenFenZhengHao)=18
老提示：
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.为什么呢？或者还有什么更简单的办法？

Answer 1

为什么不指中间那段SELECT出来看看是什么结果呢？
''''+
+substring(CONVERT(char(4),shenfenzhenghao),7,4)+'-'
+substring(CONVERT(char(2),shenfenzhenghao),11,2)+'-'
+substring(CONVERT(char(2),shenfenzhenghao),13,2)+''''

Answer 2

不用substring来substring去了。直接取了那8位数，直接可以转成时间类型了。

Answer 3

update baseinfo set chushengriqi=substring(ShenFenZhengHao,7,8)
提示：Msg 242, Level 16, State 3, Line 1
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
意思是日期超范围了？

Answer 4

加一个isdate识别一下,去除无效的数据update baseinfo 
set chushengriqi = substring(shenfenzhenghao,7,4) + '-'+ substring(shenfenzhenghao,11,2) + '-'+ substring(shenfenzhenghao,13,2)
where where ShenFenZhengHao is not null
and len(ShenFenZhengHao)=18
and isdate(substring(shenfenzhenghao,7,4) + '-'+ substring(shenfenzhenghao,11,2) + '-'+ substring(shenfenzhenghao,13,2)) = 1

Answer 5

可以转换的如下：
update baseinfo set chushengriqi=substring(ShenFenZhengHao,7,8)where ShenFenZhengHao is not null
and len(ShenFenZhengHao)=18
and SUBSTRING(shenfenzhenghao,7,4)>='1900' and SUBSTRING(shenfenzhenghao,7,2)<='2050'
and SUBSTRING(shenfenzhenghao,11,2)>='01' and  SUBSTRING(shenfenzhenghao,11,2)<='12'
and SUBSTRING(shenfenzhenghao,13,2)>='01' and  SUBSTRING(shenfenzhenghao,13,2)<='31'

Answer 6

谢谢，这个不错，去除无效数据。isdate

Answer 7

declare @id varchar(18)
set @id='110104198003110516'
select convert(datetime,substring(@id,7,8))
/*-----------------------
1980-03-11 00:00:00.000(1 行受影响)*/

Answer 8

要验证,只要这样就行了:
declare @id varchar(18)
set @id='110104198003110516'
select isdate(substring(@id,7,8))
/*
-----------
1(1 行受影响)*/