Real-Time Examples of SQL Date Functions: Practical Applications and Use Cases

Consider the emaployees table below.

Write a query to compute the age of a person, when the date of birth is given. The output should be as shown below.

Code: Function

CREATE FUNCTION fnComputeAge(@DOB DATETIME)
RETURNS NVARCHAR(50)
AS
BEGIN

DECLARE @tempdate DATETIME, @years INT, @months INT, @days INT
SELECT @tempdate = @DOB

SELECT @years = DATEDIFF(YEAR, @tempdate, GETDATE()) - CASE WHEN (MONTH(@DOB) > MONTH(GETDATE())) OR (MONTH(@DOB) = MONTH(GETDATE()) AND DAY(@DOB) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tempdate = DATEADD(YEAR, @years, @tempdate)

SELECT @months = DATEDIFF(MONTH, @tempdate, GETDATE()) - CASE WHEN DAY(@DOB) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tempdate = DATEADD(MONTH, @months, @tempdate)

SELECT @days = DATEDIFF(DAY, @tempdate, GETDATE())

DECLARE @Age NVARCHAR(50)
SET @Age = Cast(@years AS  NVARCHAR(4)) + ' Years ' + Cast(@months AS  NVARCHAR(2))+ ' Months ' +  Cast(@days AS  NVARCHAR(2))+ ' Days Old'
RETURN @Age

End

Query:

Run this below code to get the above output.

Select Id, Name, DOB, dbo.fnComputeAge(DOB) as Age from  Employee

Prerequisite Code:

Create Table Employee(
 Id int NOT NULL primary key,
 Name nvarchar(50),
 DOB datetime
)

insert into Employee values ('1', 'Rambo', '1986-11-14 08:26:00.000')
insert into Employee values ('2', 'Azam', '1984-10-10 03:32:00.000')
insert into Employee values ('3', 'Inza', '1996-07-26 08:26:00.000')
insert into Employee values ('4', 'Jaman', '1990-11-02 03:32:00.000')
insert into Employee values ('5', 'Asad', '1991-01-03 03:32:00.000')
insert into Employee values ('6', 'kamran', '1990-11-02 03:32:00.000')

Select * from Employee