Reputation: 7348

How to use DATEDIFF to return year, month and day?

How can I use DATEDIFF to return the difference between two dates in years, months and days in SQL Server 2005

DATEDIFF (date , date)

How to result that: 2 year 3 month 10 day

Can anyone complete this t-sql?

ALTER FUNCTION [dbo].[gatYMD](@dstart VARCHAR(50), @dend VARCHAR(50))
RETURNS VARCHAR(50) AS
BEGIN
    DECLARE @yy INT
    DECLARE @mm INT
    DECLARE @getmm INT
    DECLARE @dd INT

    SET @yy = DATEDIFF(yy, @dstart, @dend)
    SET @mm = DATEDIFF(mm, @dstart, @dend)
    SET @dd = DATEDIFF(dd, @dstart, @dend)
    SET @getmm = ABS(DATEDIFF(mm, DATEADD(yy, @yy, @dstart), @dend))

    RETURN (
        Convert(varchar(10),@yy) + 'year' + Convert(varchar(10),@mm) + 'month'  + Convert(varchar(10),@dd) + 'day'
        )
END

Upvotes: 2

Answers (12)

Lelo

Reputation: 912

My solution, trying to be the must human-readable as possible:

-- Define the function
ALTER FUNCTION dbo.calculate_age (@birthdate DATE, @examdate DATE) RETURNS NVARCHAR(100) AS BEGIN DECLARE @days INT,
@months INT,
@years INT;

DECLARE @birth_day INT,
@exam_day INT;

DECLARE @first_target_month INT,
@birth_month INT,
@exam_month INT;

DECLARE @second_target_year INT,
@second_target_date DATE;

DECLARE @first_target_date DATE;

-- ----------------------------------------
-- Navigate days
-- ----------------------------------------
SELECT
  @birth_day = DAY(@birthdate),
  @exam_day = DAY(@examdate),
  @first_target_month = MONTH(@birthdate);

IF @exam_day < @birth_day
SET
  @first_target_month + = 1;

DECLARE @max_day INT;

SET
  @max_day = DAY(EOMONTH(@birthdate));

-- Get the last day of the birth month
IF @exam_day > @max_day
SET
  @exam_day = @max_day;

SET
  @first_target_date = DATEFROMPARTS(YEAR(@birthdate), @first_target_month, @exam_day);

SET
  @days = DATEDIFF(DAY, @birthdate, @first_target_date);

-- ----------------------------------------
-- Navigate months
-- ----------------------------------------
SELECT
  @birth_month = MONTH(@birthdate),
  @exam_month = MONTH(@examdate);

SET
  @second_target_year = YEAR(@birthdate);

SET
  @months = @exam_month - @first_target_month;

IF @exam_month < @first_target_month BEGIN
SET
  @second_target_year + = 1;

SET
  @months + = 12;

END;

SET
  @second_target_date = DATEFROMPARTS(@second_target_year, @exam_month, @exam_day);

-- ----------------------------------------
-- Navigate years
-- ----------------------------------------
SET
  @years = YEAR(@examdate) - @second_target_year;

RETURN CONCAT(
  @years,
  ' years, ',
  @months,
  ' months, and ',
  @days,
  ' days'
);

END;

GO
  -- Select using the function
SELECT
  birth_date,
  exam_date,
  dbo.calculate_age(birth_date, exam_date) AS age_difference
FROM
  Patient;

Upvotes: 0

Kapil

Reputation: 1

-- Please have this improvised query:

DECLARE @Dt1 DateTime
DECLARE @Dt2 DateTime

SET @Dt1 = '2023-01-01 11:00:00.000'
SET @Dt2 = '2023-02-01 14:15:00.000'
SET @Dt2 = '2023-02-02 12:01:21.000'

DECLARE @year bigint, @month int, @day int
, @h int, @m int, @s int

;with ex_table AS (SELECT @Dt1 'dt1', @Dt2 'dt2')
SELECT 
  @year = DATEDIFF(yy, t.dt1, t.dt2)
, @month = DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.dt1, t.dt2), t.dt1), t.dt2)
, @day = DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.dt1, t.dt2), t.dt1), t.dt2), DATEADD(yy, DATEDIFF(yy, t.dt1, t.dt2), t.dt1)), t.dt2)
-- AS result
FROM ex_table t

select
@h = Right('0' + CONVERT(varchar(6), DATEDIFF(second, @Dt1, @Dt2)/(3600)%24), 2) --+ 'h '
, @m = RIGHT('0' + CONVERT(varchar(2), (DATEDIFF(second, @Dt1, @Dt2) % 3600) / 60), 2)
, @s = RIGHT('0' + CONVERT(varchar(2), (DATEDIFF(second, @Dt1, @Dt2) % 60)), 2)

 select @year [year], @month [mo], @day [day], @h [h], @m [mi], @s [s]

Upvotes: 0

Aaron

Reputation: 77

My answer is a bit different as I'm coming from mysql, so I'm unsure if this function works in sql server. But I'm still leaving it here in case anyone else wants it.

Select TIMESTAMPDIFF(YEAR,'2020-04-01','2022-04-01') AS Years,
TIMESTAMPDIFF(MONTH,'2020-04-01','2022-04-01') AS Months,
TIMESTAMPDIFF(DAY,'2020-04-01','2022-04-01') AS Days;

-- Sometimes this line doesn't work as you may want,
-- For example: 2022-Jan-01 to 2022-Jan-03, if you use DATEDIFF it will count 02 and 03 of the days.
-- Where as TIMESTAMPDIFF counts just the 02 of the days
-- "TIMESTAMPDIFF(DAY,'2020-04-01','2022-04-01') AS Days;"
-- Replace TIMESTAMPDIFF with DATEDIFF and remove "DAY"

The output would look something like this

  Years | Months | Days
    2   |   24   | 730

Hope it helps

Upvotes: -2

Palanikumar

Reputation: 7150

CREATE FUNCTION [dbo].[FindDateDiff](@Date1 date,@Date2 date, @IncludeTheEnDate bit)
RETURNS TABLE 
AS
RETURN 
(
    SELECT
        CALC.Years,CALC.Months,D.Days,
        Duration = RTRIM(Case When CALC.Years > 0 Then CONCAT(CALC.Years, ' year(s) ') Else '' End
                       + Case When CALC.Months > 0 Then CONCAT(CALC.Months, ' month(s) ') Else '' End
                       + Case When D.Days > 0 OR (CALC.Years=0 AND CALC.Months=0) Then CONCAT(D.Days, ' day(s)') Else '' End)
    FROM (VALUES(IIF(@Date1<@Date2,@Date1,@Date2),DATEADD(DAY, IIF(@IncludeTheEnDate=0,0,1), IIF(@Date1<@Date2,@Date2,@Date1)))) T(StartDate, EndDate)
    CROSS APPLY(Select
        TempEndYear = Case When ISDATE(CONCAT(YEAR(T.EndDate), FORMAT(T.StartDate,'-MM-dd')))=1 Then CONCAT(YEAR(T.EndDate), FORMAT(T.StartDate,'-MM-dd'))
                        Else CONCAT(YEAR(T.EndDate),'-02-28') End
    ) TEY
    CROSS APPLY(Select EndYear = Case When TEY.TempEndYear > T.EndDate Then DATEADD(YEAR, -1, TEY.TempEndYear) Else TEY.TempEndYear End) EY
    CROSS APPLY(Select
        Years = DATEDIFF(YEAR,T.StartDate,EY.EndYear),
        Months = DATEDIFF(MONTH,EY.EndYear,T.EndDate)-IIF(DAY(EY.EndYear)>DAY(T.EndDate),1,0)
    ) CALC
    CROSS APPLY(Select Days =  DATEDIFF(DAY,DATEADD(MONTH,CALC.Months,DATEADD(YEAR,CALC.Years,T.StartDate)),T.EndDate)) D
)

Sample:

Select [From] = '2021-01-01',[To] = '2021-12-31',IncludeEndDate='Yes',* From dbo.FindDateDiff('2021-01-01','2021-12-31',1)
Select [From] = '2021-01-01',[To] = '2021-12-31',IncludeEndDate='No',* From dbo.FindDateDiff('2021-01-01','2021-12-31',0)
Select [From] = '2015-12-15',[To] = '2018-12-14',IncludeEndDate='Yes',* From dbo.FindDateDiff('2015-12-15','2018-12-14',1)
Select [From] = '2015-12-15',[To] = '2018-12-14',IncludeEndDate='No',* From dbo.FindDateDiff('2015-12-15','2018-12-14',0)

Upvotes: 2

Lnr

Reputation: 197

TL;DR approximates years & months without having to create a function.

I found this question searching for "get years months from datediff". I was looking for something a bit quicker (and yeah, probably dirtier) than the above function-creating solutions.

I came up with the following in-line sql which approximates the years and months. Sufficient for my own purposes, which is getting a count of events grouped by user's rough age at the time.

select cast(datediff(event_datetime, dateofbirth)/365.25 as unsigned) as years, 
       cast((mod(datediff(event_datetime, dateofbirth),365.25))/30.4375 as unsigned) as months
from tablename
;

Upvotes: 0

Liam Wheldon

Reputation: 755

I know there are a few answers already here, but I thought I'd add what I came up with as (at least to me) it seems very simple to follow:

CREATE FUNCTION dbo.fn_DateDiff_YMDMHS
(   
    @Startdate as datetime2(0),
    @Enddate as datetime2(0)
)
RETURNS TABLE 
AS
RETURN 
(
    select 
        TotalYears [Years],
        datediff(month, dateadd(Year, TotalYears, @Startdate), @Enddate) Months,
        datediff(day, dateadd(month, TotalMonths, @Startdate), @Enddate) [Days],
        datediff(hour, dateadd(day, TotalDays, @Startdate), @Enddate) [Hours],
        datediff(minute, dateadd(hour, TotalHours, @Startdate), @Enddate) [Minutes],
        datediff(second, dateadd(minute, TotalMinutes, @Startdate), @Enddate) [Seconds]
    from (
    select 
        datediff(SECOND, @Startdate, @Enddate) TotalSeconds,
        datediff(minute, @Startdate, @Enddate) TotalMinutes,
        datediff(hour, @Startdate, @Enddate) TotalHours,
        datediff(day, @Startdate, @Enddate) TotalDays,
        datediff(month, @Startdate, @Enddate) TotalMonths,
        datediff(year, @Startdate, @Enddate) TotalYears) DateDiffs
    )

Then when you call with:

select * from dbo.fn_DateDiff_YMDMHS('1900-01-01 00:00:00', '1910-10-05 03:01:02')

You'll get this returned:

Years   Months  Days    Hours   Minutes Seconds
10      9       4       3       1       2

Obviously you could change this to a formatted output and have a scalar variable instead, but I'll leave that to you :-)

EDIT:

I've ended up needing to also do a time ago function to return a format like "5 years and 2 days ago"

CREATE FUNCTION fn_DateDiff_YMDMHS_String
(
    @StartDate datetime2(0),
    @EndDate datetime2(0),
    @OutputYears bit = 1,
    @OutputMonths bit = 1,
    @OutputDays bit = 1,
    @OutputHours bit = 0,
    @OutputMinutes bit = 0,
    @OutputSeconds bit = 0,
    @OutputSuffix bit = 0
)
RETURNS varchar(256)
AS
BEGIN
    DECLARE @Output varchar(256) = ''
    declare @Years int, @Months int, @Days int, @Hours int, @Minutes int, @Seconds int

    select 
        @Years = case when @OutputYears = 1 then Years else 0 end,
        @Months = case when @OutputMonths = 1 then Months else 0 end,
        @Days = case when @OutputDays = 1 then Days else 0 end,
        @Hours = case when @OutputHours = 1 then Hours else 0 end,
        @Minutes = case when @OutputMinutes = 1 then Minutes else 0 end,
        @Seconds = case when @OutputSeconds = 1 then Seconds else 0 end 
    from dbo.fn_DateDiff_YMDMHS(@StartDate, @EndDate)

    declare @and varchar(5) = ''
    if @OutputYears = 1 and @Years > 0 
    begin
        set @Output = @Output + cast(@Years as varchar(4)) + ' year' 
        if @Years > 1 set @Output = @Output + 's ' else set @Output = @Output + ' '
        if @Months > 0 and @Days + @Hours + @Minutes + @Seconds = 0 set @and = 'and '
    end
    if @OutputMonths = 1 and @Months > 0 
    begin
        set @Output = @Output + @and + cast(@Months as varchar(2)) + ' month'
        if @Months > 1 set @Output = @Output + 's ' else set @Output = @Output + ' '
        if @Days > 0 and @Hours + @Minutes + @Seconds = 0 set @and = 'and '
    end
    if @OutputDays = 1 and @Days > 0 
    begin
        set @Output = @Output + @and + cast(@Days as varchar(2)) + ' day'
        if @Days > 1 set @Output = @Output + 's ' else set @Output = @Output + ' '
        if @Hours > 0 and @Minutes + @Seconds = 0 set @and = 'and '
    end
    if @OutputHours = 1 and @Hours > 0 
    begin
        set @Output = @Output + @and + cast(@Hours as varchar(2)) + ' hour'
        if @Hours > 1 set @Output = @Output + 's ' else set @Output = @Output + ' '
        if @Minutes > 0 and @Seconds = 0 set @and = 'and '
    end
    if @OutputMinutes = 1 and @Minutes > 0 
    begin
        set @Output = @Output + @and + cast(@Minutes as varchar(2)) + ' minute'
        if @Minutes > 1 set @Output = @Output + 's ' else set @Output = @Output + ' '
        if @Seconds > 0 set @and = 'and '
    end
    if @OutputSeconds = 1 and @Seconds > 0 
    begin
        set @Output = @Output + @and + cast(@Seconds as varchar(2)) + ' second'
        if @Seconds > 1 set @Output = @Output + 's ' else set @Output = @Output + ' '
    end

    if @OutputSuffix = 1
    begin
        if @StartDate < @EndDate
        begin
            set @Output = @Output + 'ago'
        end
        else
        begin
            set @Output = 'in ' + @Output
        end
    end

    RETURN @Output

END

Here are 2 examples

select dbo.fn_DateDiff_YMDMHS_String('2000-01-01 00:00:00', '2007-09-19 14:39:53', 1, 1, 1, 1,1,1, 1)
7 years 8 months 18 days 8 hours 39 minutes and 53 seconds ago

and

select dbo.fn_DateDiff_YMDMHS_String('2000-01-01 00:00:00', '2007-09-19 14:39:53', 1, 1, 1, 0,0,0, 1)
7 years 8 months and 18 days ago

I hope this is of use to someone in the future anyway as I couldn't find much on anything like this when searching (could just be one of those days though) I'm open to improvements as I know I'm not always the most compact or fastest code programmer :-)

Regards

Liam

Upvotes: 3

xzmilan

Reputation: 11

Using ParseName

DECLARE
  @ReportBeginDate DATE

SET @ReportBeginDate='2015-01-01';

IF OBJECT_ID('TEMPDB..#tmp_ymd') IS NOT NULL
BEGIN
DROP TABLE #tmp_ymd;
END;

select
cast(cast(datediff(mm,@ReportBeginDate,getdate()) as decimal (10,2))/12 as decimal(10,2)) as YearMonthDec
,cast(datediff(dd,@ReportBeginDate,getdate()) as decimal (10,2)) as DayDec
into #tmp_ymd

select
YearMonthDec
,cast(parsename(YearMonthDec,2) as decimal (10,0)) as yearnum
,cast(cast(parsename(YearMonthDec,1) as decimal (10,0))/100*(12) as numeric) as monthnum
,case when YearMonthDec>=1 then datediff(dd,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(getdate())-1),getdate()),101),getdate()) else DayDec end as daynum

from #tmp_ymd

Upvotes: 1

gngolakia

Reputation: 2216

The Modified Function

ALTER FUNCTION [dbo].[gatYMD](@dstart VARCHAR(50), @dend VARCHAR(50))
RETURNS VARCHAR(50) AS
BEGIN
    DECLARE @yy INT
    DECLARE @mm INT
    DECLARE @getdd INT
    DECLARE @dd INT

    SET @yy = DATEDIFF(yy, @dstart, @dend)
    SET @mm = DATEDIFF(mm, @dstart, @dend) - (12 * @yy)
    SET @dd = ABS(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, @yy, @dstart), @dend), DATEADD(yy, @yy, @dstart)), @dend))


    Return (Convert(varchar(10),@yy) + ' year ' + Convert(varchar(10),@mm) + ' month '  + Convert(varchar(10),@dd) + ' day ')

END

Upvotes: 2

Rajesh Garg

Reputation: 61

Create this function, it will give exact date difference like year months days

    Create function get_Exact_Date_diff(@date smalldatetime,@date2 smalldatetime)
 returns varchar(50)

    as

    begin

    declare @date3 smalldatetime

    Declare @month int,@year int,@day int

     if @date>@date2
     begin
     set @date3=@date2
     set @date2=@date
     set @date=@date3
     end



    SELECT @month=datediff (MONTH,@date,@date2)

    if dateadd(month,@month,@date) >@date2
    begin
    set @month=@month-1
    end
    set @day=DATEDIFF(day,dateadd(month,@month,@date),@date2)

    set @year=@month/12
    set @month=@month % 12

    return (case when @year=0 then '' when @year=1 then convert(varchar(50),@year ) + ' year ' when @year>1 then convert(varchar(50),@year ) + ' years ' end)
    + (case when @month=0 then '' when @month=1 then convert(varchar(50),@month ) + ' month ' when @month>1 then convert(varchar(50),@month ) + ' months ' end)
    + (case when @day=0 then '' when @day=1 then convert(varchar(50),@day ) + ' day ' when @day>1 then convert(varchar(50),@day ) + ' days ' end)

    end

Upvotes: 6

L Harrison

Reputation: 31

It works for some of the situations but when you are subtracting dates like 2007-10-15 (DateHired) from 2011-01-13 (dateterminated) it gives you a negative number when the abs isn't around it but putting the abs around doesn't fix it either because then the year and month figure are incorrect.

Upvotes: 3

fbn

Reputation: 21

Check this page... http://www.sqlteam.com/article/datediff-function-demystified

Create this functions:

CREATE FUNCTION dbo.fnYearsApart
(
        @FromDate DATETIME,
        @ToDate DATETIME
)
RETURNS INT
AS
BEGIN
        RETURN  CASE
                       WHEN @FromDate > @ToDate THEN NULL
                       WHEN DATEPART(day, @FromDate) > DATEPART(day, @ToDate) THEN DATEDIFF(month, @FromDate, @ToDate) - 1
                       ELSE DATEDIFF(month, @FromDate, @ToDate)
               END / 12
END

and

CREATE FUNCTION dbo.fnMonthsApart
(
        @FromDate DATETIME,
        @ToDate DATETIME
)
RETURNS INT
AS
BEGIN
        RETURN  CASE
                       WHEN @FromDate > @ToDate THEN NULL
                       WHEN DATEPART(day, @FromDate) > DATEPART(day, @ToDate) THEN DATEDIFF(month, @FromDate, @ToDate) - 1
                       ELSE DATEDIFF(month, @FromDate, @ToDate)
               END
END

and finally

ALTER FUNCTION [dbo].[gatYMD](@dstart VARCHAR(50), @dend VARCHAR(50))
RETURNS VARCHAR(50) AS
BEGIN
    DECLARE @yy INT
    DECLARE @mm INT
    DECLARE @dd INT
    DECLARE @getmm INT
    DECLARE @getdd INT

    SET @yy = dbo.fnYearsApart(@dstart, @dend)  --DATEDIFF(yy, @dstart, @dend)
    SET @mm = dbo.fnMonthsApart(@dstart, @dend) --DATEDIFF(mm, @dstart, @dend)
    SET @dd = DATEDIFF(dd, @dstart, @dend)
    SET @getmm = ABS(DATEDIFF(mm, DATEADD(yy, @yy, @dstart), @dend))
    SET @getdd = ABS(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, @yy, @dstart), @dend), DATEADD(yy, @yy, @dstart)), @dend))

    RETURN (
      Convert(varchar(10),@yy) + ' años, ' + Convert(varchar(10),@getmm) + ' meses, '  + Convert(varchar(10),@getdd) + ' días'
    )
END

Greats!

Upvotes: 2

OMG Ponies

Reputation: 332661

Here's my solution to Eric's function:

DECLARE @getmm INT
DECLARE @getdd INT

SET @yy = DATEDIFF(yy, @dstart, @dend)
SET @mm = DATEDIFF(mm, @dstart, @dend)
SET @dd = DATEDIFF(dd, @dstart, @dend)
SET @getmm = ABS(DATEDIFF(mm, DATEADD(yy, @yy, @dstart), @dend))
SET @getdd = ABS(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, @yy, @dstart), @dend), DATEADD(yy, @yy, @dstart)), @dend))

RETURN (
  Convert(varchar(10),@yy) + 'year' + Convert(varchar(10),@getmm) + 'month'  + Convert(varchar(10),@getdd) + 'day'
)

Good call on the use of ABS to handle if the start date is after the end date.

This:

WITH ex_table AS (
  SELECT '2007-01-01' 'birthdatetime',
         '2009-03-29' 'visitdatetime')
SELECT CAST(DATEDIFF(yy, t.birthdatetime, t.visitdatetime) AS varchar(4)) +' year '+
       CAST(DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime), t.visitdatetime) AS varchar(2)) +' month '+
       CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime), t.visitdatetime), DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime)), t.visitdatetime) AS varchar(2)) +' day' AS result
  FROM ex_table t

..or non-CTE using for SQL Server 2000 and prior:

SELECT CAST(DATEDIFF(yy, t.birthdatetime, t.visitdatetime) AS varchar(4)) +' year '+
       CAST(DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime), t.visitdatetime) AS varchar(2)) +' month '+
       CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime), t.visitdatetime), DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime)), t.visitdatetime) AS varchar(2)) +' day' AS result
  FROM (SELECT '2007-01-01' 'birthdatetime',
         '2009-03-29' 'visitdatetime') t

...will return:

result
----------------------
2 year 2 month 28 day

Reference: DATEDIFF

Upvotes: 4

How to use DATEDIFF to return year, month and day?

Answers (12)

The Modified Function

Related Questions