alter function dbo.IDTrue--验证十八位身份证是否正确
(
ID varchar(18)
)
returns bit
as
begin
--验证格式是否正确
declare RES bit
declare W table (rn int,val int)
insert into W(rn,val)
select 1,7
union select 2,9
union select 3,10
union select 4,5
union select 5,8
union select 6,4
union select 7,2
union select 8,1
union select 9,6
union select 10,3
union select 11,7
union select 12,9
union select 13,10
union select 14,5
union select 15,8
union select 16,4
union select 17,2
union select 18,1
declare A table (rwn int ,val varchar(1))
insert into A(rwn,val)
select 0,'1'
insert into A(rwn,val)
select 1,'0'
insert into A(rwn,val)
select 2,'X'
insert into A(rwn,val)
select 3,'9'
insert into A(rwn,val)
select 4,'8'
insert into A(rwn,val)
select 5,'7'
insert into A(rwn,val)
select 6,'6'
insert into A(rwn,val)
select 7,'5'
insert into A(rwn,val)
select 8,'4'
insert into A(rwn,val)
select 9,'3'
insert into A(rwn,val)
select 10,'2'
declare i int
declare j int
declare S int
if len(ID)=18
begin
select S=0,i=1
declare IDI int
declare WI int
declare PII varchar(1)
while i<=17
begin
select PII=substring(ID,i,1)
if PII like '[0-9]'
begin
select IDI=convert(int,PII)
select WI=val from W where rn=i
select j=IDI*WI
select S=S+j
select i=i+1
end
else
begin
select RES=0
return RES
end
end
select S=S % 11
select IDI=convert(int,substring(ID,18,1))
declare pi varchar(1)
select pi=val from A where rwn =S
if pi=IDI
select RES=1
else
select RES=0
end
else
begin
select RES=0
end
return RES
end
go
declare pc bit
select pc=dbo.IDTrue('420324198101031224')
select pc
go
alter function SFZ15To18--十五位升十八
(
sfz varchar(15)
)
returns varchar(18)
as
begin
declare W table (rn int,val int)
insert into W(rn,val)
select 1,7
union select 2,9
union select 3,10
union select 4,5
union select 5,8
union select 6,4
union select 7,2
union select 8,1
union select 9,6
union select 10,3
union select 11,7
union select 12,9
union select 13,10
union select 14,5
union select 15,8
union select 16,4
union select 17,2
union select 18,1
declare A table (rwn int ,val varchar(1))
insert into A(rwn,val)
select 0,'1'
insert into A(rwn,val)
select 1,'0'
insert into A(rwn,val)
select 2,'X'
insert into A(rwn,val)
select 3,'9'
insert into A(rwn,val)
select 4,'8'
insert into A(rwn,val)
select 5,'7'
insert into A(rwn,val)
select 6,'6'
insert into A(rwn,val)
select 7,'5'
insert into A(rwn,val)
select 8,'4'
insert into A(rwn,val)
select 9,'3'
insert into A(rwn,val)
select 10,'2'
declare NEWID varchar(18)
select NEWID=substring(sfz,1,6)+'19'+substring(sfz,7,9)
declare i int
declare j int
declare S int
select S=0,i=1
declare IDI int
declare WI int
declare PII varchar(1)
while i<=17
begin
select PII=substring(NEWID,i,1)
if PII like '[0-9]'
begin
select IDI=convert(int,PII)
select WI=val from W where rn=i
select j=IDI*WI
select S=S+j
select i=i+1
end
else
begin
return ''
end
end
select S=S % 11
declare pi varchar(1)
select pi=val from A where rwn =S
select NEWID=NEWID+pi
return NEWID
end
go
select dbo.SFZ15To18('420324810103153')
go
--日期是否正确
alter function ChkYMD(
y int,
m tinyint,
d tinyint,
cy int
)
returns bit
as
begin
declare res bit
select res=1
if y<1900 or y>cy
begin
select res=0
return res
end
if m=1 or m=3 or m=5 or m=7 or m=8 or m=10 or m=12
begin
if (d<1) or (d>31)
begin
select res=0
return res
end
end
if m=2
begin
if ((y%4)=0) and ((y % 100)<>0) or ( (y % 400)=0 )
begin--闰年
if (d<1) or (d>29)
begin
select res=0
return res
end
end else
begin
if (d<1) or (d>28)
begin
select res=0
return res
end
end
end
if m=4 or m=6 or m=9 or m=11
begin
if (d<1) or (d>30)
begin
select res=0
return res
end
end
return res
end
go
select dbo.chkymd(1981,1,3,Year(getdate()))
go
/*
y:年,m:月,d:日。在参数都只传入相应的整数
返回值:0 星期一
1 星期2
2 星期3
3 星期4
4 星期5
5 星期6
6 星期7
*/
alter function GetWeekDay(
y int,
m int,
d int
)
returns tinyint
as
begin
declare a tinyint
select a=7
if m=1 or m=2
begin
select m=m+12
select y=y-1
end
select a=(d+2*m+3*(m+1)/5+y+y/4-y/100+y/400)%7;
return a
end
go
select dbo.getweekday(2004,12,10)
身份证验证
80酷酷网 80kuku.com