Real time example SQL Date Function
Table of Content:
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