If the age is older than 18, then save the Customer record

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.

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