declare @id varchar(18)set @id='310501198408097619'
select cast(substring(@id,7,4)+'-'+substring(@id,11,2)+'-'+substring(@id,13,2) as datetime)
-- 1984-08-09
解决方案 »
- 另开一个
- 为什么我装了SQL后 可它不启用 啊
- 怪!sql server 2005 中 "未能找到存储过程:***"
- sum 运算不能以 varchar 数据类型作为参数。
- 为什么总是不能连接远程SQL Server啊?ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
- 一个很菜的问题:自动增长的主键无限增长会溢出吗?
- t-sql 难题 求高手
- 求大家帮忙——关于数据库内计算
- 请高手看看调用存储过程中参数的问题?
- 只欠东风?(急用)
- MS-SQL Server 设置复合主键到底有什么用?
- [DBNETLIB][ConnectionWrite (send()).]一般性网络错误。请检查网络文档
提示公式验证错误公式我也有 就是不知道怎么加入
还是谢谢你
declare @id varchar(18)set @id='3105018408097619'
if Len(@id)=18
begin
select substring(@id,7,4)+'-'+substring(@id,11,2)+'-'+substring(@id,13,2)
end
else --存在旧的身份证号码
begin
select '19'+substring(@id,7,2)+'-'+substring(@id,9,2)+'-'+substring(@id,11,2)
end
create function getDate(@id varchar(18))
returns datetime
as
begin
declare @getdate datetime
if Len(@id)=18
begin
set @getdate= cast((substring(@id,7,4)+'-'+substring(@id,11,2)+'-'+substring(@id,13,2))as datetime)
end
else
begin
set @getdate= cast(('19'+substring(@id,7,2)+'-'+substring(@id,9,2)+'-'+substring(@id,11,2)) as datetime)
end
return @getdate
endselect dbo.getDate('3105018408097619')
gocreate table t1
(
身份证 varchar(18),
生日 as case LEN(身份证) when 18
then substring(身份证,7,4)+'-'+substring(身份证,11,2)+'-'+substring(身份证,13,2)
else '19'+substring(身份证,7,2)+'-'+substring(身份证,9,2)+'-'+substring(身份证,11,2)
end
)
goinsert t1 select '310501198408097619'
goselect * from t1
go
/**
身份证 生日
----------------------------------
310501198408097619 1984-08-09
**/drop table t1
go
UPDATE table SET 出生年月=dbo.getDate(身份证号码)
if Len(@id)=18
begin
select substring(@id,7,4)+'-'+substring(@id,11,2)+'-'+substring(@id,13,2)
end
else --存在旧的身份证号码
begin
select '19'+substring(@id,7,2)+'-'+substring(@id,9,2)+'-'+substring(@id,11,2)
end
if Len(@id)=18
begin
select cast(substring(@id,7,8) as datetime ) as birthdate
end
else
begin
select cast('19'+substring(@id,7,6) as datetime)as birthdate
end
cast(substring(身份证,7,4)+'-'+substring(身份证,11,2)+'-'+substring(身份证,13,2) as datetime)
或
substring(身份证,7,4)+'-'+substring(身份证,11,2)+'-'+substring(身份证,13,2)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOCREATE FUNCTION [dbo].[Birthday]
(
@IDCardNo NVARCHAR(50)
)
RETURNS SMALLDATETIME
AS
BEGIN
DECLARE @Birthday SMALLDATETIME
SET @Birthday= (CASE WHEN LEN(@IDCardNo)=15 THEN CONVERT(VARCHAR(10),SUBSTRING(@IDCardNo,7,2)+'-'+SUBSTRING(@IDCardNo,9,2)+'-'+SUBSTRING(@IDCardNo,11,2),120)
WHEN LEN(@IDCardNo)=18 THEN CONVERT(VARCHAR(10),SUBSTRING(@IDCardNo,7,4)+'-'+SUBSTRING(@IDCardNo,11,2)+'-'+SUBSTRING(@IDCardNo,13,2),120)
END)
RETURN(@Birthday)
END調用測試:
--15位身份證
SELECT [dbo].[Birthday]('512223700101104')
-----------------------
--1970-01-01 00:00:00--18位身份證
SELECT [dbo].[Birthday]('310501198408097619 ')
-----------------------
--1984-08-09 00:00:00