Sunny Books
What we have

SQL Server Date and Time Functions 2

This is the second part of SQL Server Data and Time functions. we talk about DATEADD, DATEDIFF, and EOMONTH.

DATEADD(datepart, number, date): returns the datatime after adding a specified number to the datepart of the given date. Note: the number can be year, month, day, minute, etc.

DATEDIFF (datepart, start_date, end_date): returns the count of the specified datepart boundaries crossed between the specified start date and end date.

EOMONTH(start_date, [month_to_add]): Returns the last day of the month that contains the specified date, with an optional offset.

datepart abbreviation
year yy,yyyy
quarter qq,q
month mm,m
dayofyear dy,y
day dd,d
week wk,ww
weekday dw
hour hh
minute mi,n
second ss,s
millisecond ms
microsecond mcs
nanosecond ns
Tzoffset tz

Example

SELECT DATEADD(DAY, 20, '1980-12-20 09:25:07.000') returns 1981-01-09 09:25:07.000
SELECT DATEADD(DAY, -20, '1980-12-20 09:25:07.000') returns 1980-11-30 09:25:07.000

SELECT DATEDIFF(YEAR, '1980-12-20 09:25:07.000', '2015-8-29') returns 35
SELECT DATEDIFF(YEAR, '2015-8-29', '1980-12-20 09:25:07.000') returns -35
SELECT DATEDIFF(MONTH, '2015-8-29 16:00:00', '1980-12-20 09:25:07.000') returns -416
SELECT DATEDIFF(MONTH, '1980-12-20 09:25:07.000', '2015-8-29 16:00:00') returns 416

SELECT EOMONTH ('2015-8-29 16:00:00') returns 2015-08-31
SELECT EOMONTH ('2015-8-29 16:00:00', 6) returns 2016-02-29

Example: caculate age

CREATE function fn_getage (@birthday DATETIME)
RETURNS nvarchar(50)
AS
BEGIN
  DECLARE @tempdate DATETIME, @years int, @months int, @days int
  SELECT @tempdate = @birthday
  SELECT @years = DATEDIFF(YEAR, @tempdate, GETDATE()) -
    CASE
      WHEN (MONTH(@birthday) > MONTH(GETDATE())) OR
      MONTH(@birthday) = MONTH(GETDATE()) AND DAY(@birthday) >DAY(GETDATE() ))
      THEN 1 ELSE 0
    END
  SELECT @tempdate = DATEADD(YEAR, @years, @tempdate)
  SELECT @months = DATEDIFF(MONTH, @tempdate, GETDATE()) -
    CASE
      WHEN DAY(@birthday) > 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
SELECT name, birthday, dbo.fn_getage(birthday) as age
FROM [em].[employee]
SUNWEB EXPERT