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(varc开发者_如何学编程har(10),@dd) + 'day'
)
END
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
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
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.
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
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!
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
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
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)
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
;
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
加载中,请稍侯......
精彩评论