January 5, 2011 at 1:56 am
Hi all,
how to get difference in year month and days from 2 date values?
Ex:10/aug/2010,12/oct/2010 then result is
Res:0 year,2 month,2 days
January 5, 2011 at 2:12 am
Hi..Try this
select cast(datediff(yyyy, '2006-01-01', '2008-01-01') as varchar) + 'Y : '
+ cast(datediff(mm, '2006-01-01', '2008-01-01') as varchar) + 'M : '
+ cast(datediff(dd, '2006-01-01', '2008-01-01') as varchar) + 'D'
January 5, 2011 at 2:24 am
Junglee_George (1/5/2011)
Hi..Try thisselect cast(datediff(yyyy, '2006-01-01', '2008-01-01') as varchar) + 'Y : '
+ cast(datediff(mm, '2006-01-01', '2008-01-01') as varchar) + 'M : '
+ cast(datediff(dd, '2006-01-01', '2008-01-01') as varchar) + 'D'
Right idea, Junglee, but you're going to get 24 months there, when it should be 2 years, 0 months, 0 days.
You'll need to do something like this:
DECLARE @date1 DATETIME,
@date2 DATETIME
SELECT @date1 = '1/1/2008',
@date2 = '4/12/2010'
SELECT
DATEDIFF( mm, @date1, @date2) / 12 AS years
, DATEDIFF( mm, @date1, @date2) % 12 AS months
, DATEDIFF( dd, DATEADD( mm, DATEDIFF( mm, @date1, @date2), @date1), @date2)
Because we're not looking for the actual year crossing of 12/31 - 1/1, you need to derive the # of years from the # of months differentiating the values. Then the # of months is the remainder of the division by 12. The reason for the dateadd/datediff for days is that we have to advance the @date1 forward to just give us a difference in days.
Problem is, this is incomplete. It won't handle inverted days well. To see what I mean, invert the date1/2 to this:
SELECT @date1 = '4/12/2008',
@date2 = '1/1/2010'
If the above solves your problem, well and good. If not, I've got to go find some code that I wrote up at one point to deal with this. It gets really messy.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 5, 2011 at 2:46 am
It give result like 0Y : 2M : 63D
I need the result like:2M:2days (2010-08-10,2010-10-12)
January 5, 2011 at 3:02 am
Thank you craig.
It may solve my issus
February 3, 2011 at 3:49 am
try this
--By Rajat Bhalla
ALTER FUNCTION [dbo].[fnGetDateDiffAsYMD] (@FromDate AS DateTime,@ToDate AS DATETIME)
--Year,Date and Month section modified By ---------------Rajat Bhalla--------------------
RETURNS VARCHAR(30)
AS
BEGIN
DECLARE @date datetime,
@tmpdate datetime,
@years int,
@months int,
@days int,
@exp varchar(30),
@mm int,
@experiance datetime
if (datediff(dd,@FromDate ,@ToDate)< 0) or (@FromDate='') or (@ToDate is null)
select @exp ='Invalid joining date'
else
begin
select @experiance=Dateadd(yy,Datediff(yy,@FromDate,@ToDate),@fromDate)
select @years=Datediff(yy,@FromDate,@ToDate) - (CASE
WHEN @experiance > @ToDate THEN 1
ELSE 0
END)
select @months=Month(@ToDate - @experiance) -1
select @days = Day(@ToDate - @experiance) - 1
if @years<=0 and @months<=0 and @days<=0
set @exp = '0';
else if @years<=0
begin
if @months>0
begin
if @days>0
begin
if @months>1
begin
if @days>1
set @exp= CAST(@months as varchar) + ' months' +' and ' + CAST(@days as varchar) + ' Days'
else if @days=1
set @exp= CAST(@months as varchar) + ' months' +' and ' + CAST(@days as varchar) + ' Day'
end
else if @months=1
begin
if @days>1
set @exp= CAST(@months as varchar) + ' month' +' and ' + CAST(@days as varchar) + ' Days'
else if @days=1
set @exp= CAST(@months as varchar) + ' month' +' and ' + CAST(@days as varchar) + ' Day'
end
end
else if @days<=0
begin
if @months>1
set @exp= CAST(@months as varchar) + ' months'
else if @months=1
set @exp= CAST(@months as varchar) + ' month'
end
end
else if @months<=0
if @days>1
set @exp = CAST(@days as varchar) + ' Days'
else if @days=1
set @exp = CAST(@days as varchar) + ' Day'
end
else if @years>0 and @months>0 and @days>0
begin
if @years>1
begin
if @months>1
begin
if @days>1
set @exp= CAST(@years as varchar) +' Years' + ', '+ CAST(@months as varchar) + ' months' +' and ' + CAST(@days as varchar) + ' Days'
else if @days=1
set @exp= CAST(@years as varchar) +' Years' + ', '+ CAST(@months as varchar) + ' months' +' and ' + CAST(@days as varchar) + ' Day'
end
else if @months=1
begin
if @days>1
set @exp= CAST(@years as varchar) +' Years' + ', '+ CAST(@months as varchar) + ' month' +' and ' + CAST(@days as varchar) + ' Days'
else if @days=1
set @exp= CAST(@years as varchar) +' Years' + ', '+ CAST(@months as varchar) + ' month' +' and ' + CAST(@days as varchar) + ' Day'
end
end
else if @years=1
begin
if @months>1
begin
if @days>1
set @exp= CAST(@years as varchar) +' Year' + ', '+ CAST(@months as varchar) + ' months ' +'and ' + CAST(@days as varchar) + ' Days'
else if @days=1
set @exp= CAST(@years as varchar) +' Year' + ', '+ CAST(@months as varchar) + ' months ' +'and ' + CAST(@days as varchar) + ' Day'
end
else if @months=1
begin
if @days>1
set @exp= CAST(@years as varchar) +' Year' + ', '+ CAST(@months as varchar) + ' month ' +'and ' + CAST(@days as varchar) + ' Days'
else if @days=1
set @exp= CAST(@years as varchar) +' Year' + ', '+ CAST(@months as varchar) + ' month ' +'and ' + CAST(@days as varchar) + ' Day'
end
end
end
else if @years>0 and @days>0 and @months<=0
begin
if(@years>1)
begin
if(@days>1)
set @exp = CAST(@years as varchar) +' Years' +' and ' + CAST(@days as varchar) + ' Days'
else if(@days=1)
set @exp = CAST(@years as varchar) +' Years' +' and ' + CAST(@days as varchar) + ' Day'
end
else if(@years=1)
begin
if(@days>1)
set @exp = CAST(@years as varchar) +' Year' +' and ' + CAST(@days as varchar) + ' Days'
else if(@days=1)
set @exp = CAST(@years as varchar) +' Year' +' and ' + CAST(@days as varchar) + ' Day'
end
end
else if @years>0 and @days<=0 and @months<=0
begin
if @years>1
set @exp = CAST(@years as varchar) +' Years'
else if @years=1
set @exp = CAST(@years as varchar) +' Year'
end
else if @years>0 and @days<=0 and @months>0
begin
if @years>1
begin
if @months>1
set @exp = CAST(@years as varchar) +' Years' +' and ' + cast(@months as varchar) + ' months'
else if @months=1
set @exp = CAST(@years as varchar) +' Years' +' and ' + cast(@months as varchar) + ' month'
end
else if @years=1
begin
if @months>1
set @exp = CAST(@years as varchar) +' Year' +' and ' + cast(@months as varchar) + ' months'
else if @months=1
set @exp = CAST(@years as varchar) +' Year' +' and ' + cast(@months as varchar) + ' month'
end
end
end
return @exp
END
October 29, 2012 at 8:33 am
Try This.
DECLARE @date1DATETIME,
@date2DATETIME,
@yearINT,
@monthINT,
@daysINT,
@hoursINT,
@min-2INT,
@secINT
SELECT@date1 = '2012-10-29 18:34:31.013',
@date2 = '2013-12-29 20:54:41.056'
select@year=DATEDIFF(yy,@date1, @date2),
@date1=DATEADD(yy,@year,@date1),
@month=DATEDIFF(MM,@date1, @date2),
@date1=DATEADD(MM,@month,@date1),
@days=DATEDIFF(DD,@date1, @date2),
@date1=DATEADD(dd,@days,@date1),
@hours=DATEDIFF(hh,@date1, @date2),
@date1=DATEADD(hh,@hours,@date1),
@min-2=DATEDIFF(MI,@date1, @date2),
@date1=DATEADD(mi,@min,@date1),
@sec=DATEDIFF(SS,@date1, @date2)
SELECTCASE WHEN @year= 0 THEN '' ELSE CAST (@yearAS VARCHAR)+'Y 'END+
CASE WHEN @month= 0 THEN '' ELSE CAST (@monthAS VARCHAR)+'M 'END+
CASE WHEN @days= 0 THEN '' ELSE CAST (@daysAS VARCHAR)+'D 'END+
CASE WHEN @hours= 0 THEN '' ELSE CAST (@hoursAS VARCHAR)+'H 'END+
CASE WHEN @min-2= 0 THEN '' ELSE CAST (@minAS VARCHAR)+'Mi 'END+
CASE WHEN @sec= 0 THEN '' ELSE CAST (@secAS VARCHAR)+'Sec 'END
February 25, 2013 at 1:02 pm
Getting the months and years between two dates using Datediff
There is a round up issue with the Datediff function.
When the reporting date month is the same as the target month you can get a round up
For example using a report date of 2012-05-06
And looking for the age of a person (years and months only)
This code looks like it should work:
declare @AsOnDate datetime
declare @mdob datetime
set @AsOnDate = '2012-05-06' --report date or from datetime function
set @mdob = '2011-05-01' --yyyy,mm,dd Birth Dates for examples
Select
"Years" = (datediff(month,@mdob,@AsOnDate)/12) , -- Integer Div to get years from total months
"Months" = (datediff(month,@mdob,@AsOnDate)% 12) -- Mod Div to get remaining months
However we get this results for a report date of '2012-05-06' and Birth Date of :
2011-05-01 we get 1 year 0 months - correct - report is 5 days after birthday
2011-05-06 we get 1 year 0 months - correct - report is on birthday
2011-05-26 we get 1 year 0 months - Wrong - report is 20 days before birthday should be 0 Years 11 months
This round up is very easy to miss!! :unsure:
To correct this we need to check the dates and if the report month and target month are the same then we need to correct for the round up by reducing a month if the target day is greater than the report day (not a full month yet). The following code corrects for this.
declare @AsOnDate datetime
declare @mdob datetime
set @AsOnDate = '2012-05-06' --report date or from datetime function
set @mdob = '2011-05-07' --yyyy,mm,dd Birth Date for example
Select
"Years" = CASE
WHEN (DATEPART( DAY , @mdob)) > (DATEPART( DAY , @AsOnDate))
and (DATEPART( Month,@mdob)) = (DATEPART(Month, @AsOnDate))
THEN ((datediff(month,@mdob,@AsOnDate)-1)/12)
ELSE (datediff(month,@mdob,@AsOnDate)/12)
END,
"Months" = CASE
WHEN (DATEPART( DAY , @mdob)) > (DATEPART( DAY , @AsOnDate))
and (DATEPART( Month,@mdob)) = (DATEPART( Month,@AsOnDate))
THEN ((datediff(month,@mdob,@AsOnDate)-1)% 12)
ELSE (datediff(month,@mdob,@AsOnDate)% 12)
END
Results with correction:
2011-05-01 we get 1 year 0 months - correct - report is 5 days after birthday
2011-05-06 we get 1 year 0 months - correct - report is on birthday
2011-05-26 we get 0 years 11 months - correct - report is 20 days before birthday
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply