I want to create a function, which checks the age from by the entry a record. if the age is older than 18, then save the record. If not, don’t save the record.
create function f_Over18 (@age date)
returns char (20)
as begin
--declare @returnOne int
declare @date int
set @date= year(getdate()) - year(@age)
if (@date > 17)
begin
print ('Age verified') --this is only an example but i want, that safes the record
end
else
begin
print ('Age not verified')
end;
end;
Thanks a lot for your kind help.
Advertisement
Answer
The question really is about how to compute an age from a date of birth in SQL Server. It is not that easy, since there is no built-in for that, and because functions such as datediff()
cannot really give an accurate results (or at least not without many convolutions).
A simple and efficient method is to convert the date of birth and the current date to format YYYYMMDD
, turn that to a string, then use simple arithmetics, like so:
(convert(int, convert(char(8), getdate(), 112)) - convert(char(8), @dob, 112)) / 10000
In your function:
create function f_Over18 (@dob date)
returns nvarchar (20)
as begin
declare @age int;
declare @res nvarchar(20);
set @age=
(convert(int, convert(char(8), getdate(), 112)) - convert(char(8), @dob, 112))
/ 10000;
if (@age > 17)
begin
set @res = 'Age verified';
end
else
begin
set @res = 'Age not verified';
end;
return concat(@res, ': ', @age);
end;
I slightly modified the original code so it compiles properly, and returns the computed date as well (which makes it easy to debug).
Now we can test:
dob | res :--------- | :------------------- 2000-01-01 | Age verified: 20 2002-06-11 | Age verified: 18 2002-06-13 | Age not verified: 17 2010-01-01 | Age not verified: 10