DatePart, DateAdd and DateDiff functions in SQL Server - Part 27

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.









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.


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



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

14 comments:

  1. Sir very long back when i visited your blog, I am not sure, But i felt like i have seen javascript vedio tutorials.. If I am not wrong, please upload it again sir....

    ReplyDelete
  2. Hello sir,thanks a lot for the videos....but sorry to say that some vedeos are not visible that means i am not able to see the query i.e. all are grayed out

    ReplyDelete
    Replies
    1. If the videos are not vicible properly, increse resolution of the video (right bottom cornor-->gear like symbol to 360/480)

      Delete
  3. if the persons DOB is greater than GETDATE() value then the above function will fail
    you need to check using if condition

    wasim

    ReplyDelete
    Replies
    1. listen brother GETDATE() is always grater then to DOB
      if DOB is grater then GETDATE() means
      that person is not born
      that is a feature thing
      if DOB and GETDATE() are same then datediff() gives zero
      and IF DOB is grater then GETDATE() means age goes -ve
      world knows age, energy,mass like these thing never have -ve value

      Delete
  4. Your video tutorials are of great help to any serious developer,It helps to clear concepts with clarity.
    Thanks for all the effort you have put up to group the tutorials in logical order.

    ReplyDelete
  5. When I am executing function dbo.fnComputeAge in the end it is showing an error as 'Arithmetic overflow error converting expression to data type nvarchar.'
    Please tell me whats wrong with this

    ReplyDelete
  6. I am getting this error.. Can any one guide me how to resolve this???

    Msg 8115, Level 16, State 2, Line 1
    Arithmetic overflow error converting expression to data type nvarchar.

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

    insert into Employee values ('1', 'Atique', '1986-11-14 08:26:00.000')
    insert into Employee values ('2', 'Imran', '1984-10-10 03:32:00.000')
    insert into Employee values ('3', 'Hassan', '1996-07-26 08:26:00.000')
    insert into Employee values ('4', 'Khawar', '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', 834783, '1990-11-02 03:32:00.000')



    select * from Employee

    Create Function ComputeEmpolyeesAge (@DOB DateTime)
    Returns NVARCHAR(50)
    AS
    BEGIN
    Declare @tempdate Datetime, @years Int, @month Int, @day Int
    Select @tempdate = @DOB

    Select @years = DATEDIFF (YEAR, @tempdate, GETDATE()) - Case When
    (Month (@DOB) > (GETDATE())) OR MONTH(@DOB) = Month(GETDATE()) AND DAY(@DOB) > DAY(GETDATE()) THEN 1 ELSE 0 END
    Select @tempdate = DATEADD (YEAR, @years, @tempdate)

    Select @month =DATEDIFF (Month, @tempdate, GETDATE()) - CASE WHEN
    Day(@DOB) > Day(GetDate()) THEN 1 ELSE 0 END
    Select @tempdate = DATEADD(Month,@month, @tempdate)

    Select @day = DATEDIFF(Day, @tempdate, @DOB)

    Declare @Age Nvarchar(50)
    Set @Age = Cast (@years as Nvarchar(4)) + 'Years' + cast (@month as nvarchar (2)) + 'Montbs' + Cast(@day as nvarchar(2)) + 'Days Old'
    Return @Age
    END
    Select Name, DOB, dbo.ComputeEmpolyeesAge(convert (nvarchar,'1991-12-23 10:14:00.067')) as Age
    from Employee

    ReplyDelete
    Replies
    1. Pls check, getting days count in your function.

      Select @day = DATEDIFF(Day, @tempdate, @DOB)
      in above code instead of @DOB you can use GETDATE()


      Delete
  7. how do i can take this query into a trigger that check the age and rollback when it's not in the range?

    ReplyDelete
  8. select ISDATE('sunanda');--0
    select ISDATE('23:52:41.8962531');--0
    select ISDATE(GETDATE());--1
    select ISDATE('2018-12-15');--1
    select ISDATE('12-15-2018');--1
    select ISDATE('12-15-2018 23:52:41.896');--1
    select ISDATE('12-15-2018 23:52:41.8964124');--0
    select ISDATE('12-15-2018 23:52:41.8964124 +12:00');--0

    --NOTE time,datetime2 & datetimeoffset it retuens ZERO

    ---------------------------------------------------------
    --retuens DAY or MONTH or YEAR number of perticular date
    --DAY()

    select DAY('1996-04-15') --DAY 15
    select MONTH('1996-04-15') --DAY 04
    select YEAR('1996-04-15') --DAY 1996
    select DAY(GETDATE())
    select MONTH(GETDATE())
    select YEAR(GETDATE())

    --------------------------------------------
    --returns the string that part of pericular string
    --DATENAME(daypart,date)

    select DATENAME(Weekday,Getdate())
    select DATENAME(month,Getdate())
    select DATENAME(year,Getdate())
    select DATENAME(hour,Getdate())
    select DATENAME(minute,Getdate())
    select DATENAME(second,Getdate())
    select DATENAME(millisecond,Getdate())
    select DATENAME(microsecond,Getdate())
    select DATENAME(nanosecond,Getdate())

    select DATENAME(QUARTER,Getdate())
    select DATENAME(QUARTER,'03-25-2018')
    select DATENAME(QUARTER,'06-25-2018')
    select DATENAME(QUARTER,'09-25-2018')
    select DATENAME(QUARTER,'12-25-2018')

    select DATENAME(week,'01-01-2018')
    select DATENAME(week,'01-31-2018')
    select DATENAME(week,'02-01-2018')
    select DATENAME(week,'02-21-2018')
    select DATENAME(week,'02-28-2018')
    select DATENAME(week,'12-31-2018')

    select DATENAME(DAYOFYEAR,'01-01-2018')
    select DATENAME(DAYOFYEAR,'12-31-2018')

    select DATENAME(TZOFFSET,'12-31-2018')
    -----------------------------------------
    --returns the integer that part of pericular string
    --DATEPART(daypart,date)

    select DATEPART(Weekday,Getdate())
    select DATEPART(month,Getdate())
    select DATEPART(year,Getdate())
    select DATEPART(hour,Getdate())
    select DATEPART(minute,Getdate())
    select DATEPART(second,Getdate())
    select DATEPART(millisecond,Getdate())
    select DATEPART(microsecond,Getdate())
    select DATEPART(nanosecond,Getdate())

    select DATEPART(QUARTER,Getdate())
    select DATEPART(QUARTER,'03-25-2018')
    select DATEPART(QUARTER,'06-25-2018')
    select DATEPART(QUARTER,'09-25-2018')
    select DATEPART(QUARTER,'12-25-2018')

    select DATEPART(week,'01-01-2018')
    select DATEPART(week,'01-31-2018')
    select DATEPART(week,'02-01-2018')
    select DATEPART(week,'02-21-2018')
    select DATEPART(week,'02-28-2018')
    select DATEPART(week,'12-31-2018')

    select DATEPART(DAYOFYEAR,'01-01-2018')
    select DATEPART(DAYOFYEAR,'12-31-2018')

    select DATEPART(TZOFFSET,'12-31-2018')


    ---------------------------------------
    --comaprision of DatePart & Date Name

    select DATENAME(month,Getdate())
    select DATENAME(year,Getdate())
    select DATENAME(weekday,Getdate())

    select DATEPART(month,Getdate())
    select DATEPART(year,Getdate())
    select DATEPART(weekday,Getdate())

    -------------------------------------------
    --DateAdd(datepart,NumberTOadd,Add)

    select DateAdd(month,10,'2018-02-21')--it will add 10 mnths 2018-12-21 00:00:00.000
    select DateAdd(year,10,'2018-02-21')--it will add 10 YEARS 2028-02-21 00:00:00.000
    select DateAdd(weekday,10,'2018-02-21')--it will add 10 Days 2018-03-03 00:00:00.000

    select DateAdd(month,-10,'2018-02-21')--it will remove 10 mnths 2017-04-21 00:00:00.000
    select DateAdd(year,-10,'2018-02-21')--it will remove 10 YEARS 2008-02-21 00:00:00.000
    select DateAdd(weekday,-10,'2018-02-21')--it will remove 10 Days 2018-02-11 00:00:00.000

    ReplyDelete
  9. Hi, I am getting this error "Arithmetic overflow error converting expression to data type nvarchar."
    I did exactly what this video says.
    Thank you

    ReplyDelete
  10. Hi I have to calculate the financial year .. which start from 1 July and ends with 30 June ..how to calculate..

    ReplyDelete
  11. can any one explain case statement with dob(2020-09-02)

    ReplyDelete

It would be great if you can help share these free resources