alter FUNCTION dbo. idtrue--Verify that the 18-bit ID card is correct
(
@ID varchar (18)
)
Returns bit
As
Begin
--Verify that the format is correct
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--15-bit + 18
(
@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) + ' +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
--date is correct
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%) <>0) or ((@y% 400) =0)
begin--Leap Year
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: Year, M: month, D: Day. Only the appropriate integers are passed in the arguments
Return value: 0 weeks A
1 weeks 2
2 Weeks 3
3 weeks 4
4 weeks 5
5 weeks 6
6 Weeks 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)
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.