DatePart(DatePart, Date) - Returns an integer representing the specified DatePart. This function is simialar to DateName(). DateName() returns nvarchar, where as DatePart() returns an integer. The valid DatePart parameter values are shown below.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZ3oSZpLdnsrP6D-w6IbbftL0rUbgzKby2hanxl6zr6RRcU2Ll6IW3hNy4_Zax_xDQfmF0GJjqtVpcELar-aPTrKGmB5zY_nplqwiVvO64jXr6CCOf6dINtE3ntBrAOWEZcdCQnLcsDqCL/s1600/DatePart+valid+values.png)
Examples:
Select DATEPART(weekday, '2012-08-30 19:45:31.793') -- returns 5
Select DATENAME(weekday, '2012-08-30 19:45:31.793') -- returns Thursday
DATEADD (datepart, NumberToAdd, date) - Returns the DateTime, after adding specified NumberToAdd, to the datepart specified of the given date.
Examples:
Select DateAdd(DAY, 20, '2012-08-30 19:45:31.793')
-- Returns 2012-09-19 19:45:31.793
Select DateAdd(DAY, -20, '2012-08-30 19:45:31.793')
-- Returns 2012-08-10 19:45:31.793
DATEDIFF(datepart, startdate, enddate) - Returns the count of the specified datepart boundaries crossed between the specified startdate and enddate.
Examples:
Select DATEDIFF(MONTH, '11/30/2005','01/31/2006') -- returns 2
Select DATEDIFF(DAY, '11/30/2005','01/31/2006') -- returns 62
Consider the emaployees table below.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEggRg4yfZ3syG68issyS2S41BpEs907No3Hl_u6cWJdGWLpO7CPDehKyvLbO7WwISHGXqYzW3_NAxmopVyKaOxFZZ1UawCbr0xKZ6XQ_9LSprnNSzSLUZFUeIoMy_TH34EoAN1g4276GGfZ/s1600/Employee+Table.png)
Write a query to compute the age of a person, when the date of birth is given. The output should be as shown below.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhRFerL7Irv_1ZF6WUQasTGqR04oYEligzGcPRLuQ0otvJK_FjZdNfDrrA1L9v0VFGSMqEmmyXk-9D6Eus8UF-FP07IR_CJmq2OOvG9FsdbE9euDGmFVfVWmicf1EWqwxekv2xryS67ZM3o/s1600/DateDiff+function+example.png)
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
Using the function in a query to get the expected output along with the age of the person.
Select Id, Name, DateOfBirth, dbo.fnComputeAge(DateOfBirth) as Age from tblEmployees
0 comments:
Post a Comment
Note: only a member of this blog may post a comment.