January 17, 2012 at 1:34 am
I've tryed several tsql scripts and none of them have proven to be accurate.
If you got a neat script up your sleeve, i'd appreciate.
Someting like this http://www.sqlservercentral.com/scripts/Age/75784/
(which doesnt work for ('2011-01-16', '2013-01-01') )
/*----------------------------------------------------------------------------------------------------------------------------
Author :-ANAND BABU UCHAGAWKAR
Purpose:-To find the datediff/age in text format (eg. 1 year(s), 10 month(s), 10 day(s)).
DATE:-30-Aug-2011
DATABASE:- SQL
----------------------------------------------------------------------------------------------------------------------------*/
IF (Select COUNT(*) From Sysobjects Where [name] like 'FN_GETDATEDIFFTEXT') > 0
BEGIN
DROP FUNCTION FN_GETDATEDIFFTEXT
END
GO
CREATE FUNCTION FN_GETDATEDIFFTEXT(@FromDate DateTime, @ToDate DateTime)
RETURNS NVARCHAR(50)
AS
BEGIN
Declare @daysDiff Int
Declare @monthDiff Int
Declare @yearDiff Int
--Select @daysDiff = DATEDIFF(DAY, @FromDate, @ToDate)
Set @monthDiff = ABS(DATEDIFF(MONTH, @FromDate, @ToDate)%12)
Set @yearDiff = ABS(DATEDIFF(YYYY, @FromDate, @ToDate))
-- If the From date month is greater than the month of the To date and the year difference is greater than zero
-- then the year should the deducted by one
IF DATEPART(MONTH,@FromDate) > DATEPART(MONTH,@ToDate) AND @yearDiff > 0
BEGIN
Set @yearDiff = @yearDiff - 1
END
IF DATEPART(DAY,@FromDate) > DATEPART(DAY, @ToDate)
Begin
--Get last date of the month of the FromDate
Declare @lastDateOfMonth DateTime = DATEADD(MONTH, 1, @FromDate)
Set @lastDateOfMonth = '01-' + DATENAME(MONTH,@lastDateOfMonth) + '-'+DATENAME(YEAR,@lastDateOfMonth)
Set @lastDateOfMonth = DATEADD(DAY, -1, @lastDateOfMonth)
Set @daysDiff = DATEDIFF(DAY, @FromDate, @lastDateOfMonth)
Set @daysDiff = @daysDiff + DATEPART(DAY, @ToDate)
Set @monthDiff = @monthDiff - 1
End
ELSE
BEGIN
Set @daysDiff = DATEPART(DAY, @ToDate) - DATEPART(DAY, @FromDate)
END
-- Select @yearDiff Yr, @monthDiff Mn, @daysDiff Dy
RETURN
CAST(@yearDiff as nvarchar) + ' year(s), ' +
CAST(@monthDiff as nvarchar) + ' month(s), ' +
CAST(@daysDiff as nvarchar) + ' day(s)'
END
GO
Select DBO.FN_GETDATEDIFFTEXT('2011-01-16', '2013-01-01')
-- Select DBO.FN_GETDATEDIFFTEXT('01-Jan-1990', Getdate())
January 17, 2012 at 10:56 am
Just threw this together from scratch. Test it out and see if you can break it.
@date1 should always be prior to or the same as @date2
declare @date1 date = '2012-01-15'
declare @date2 date = '2013-01-16'
declare @years int
declare @months int
declare @days int
select @years = DATEDIFF(YEAR,@date1,@date2) - case when datepart(day,@date1) > datepart(day,@date2) then 1 else 0 end
select @months = DATEdiff(month,@date1,@date2) % 12 - case when datepart(day,@date1) > datepart(day,@date2) then 1 else 0 end
select @months = case when @months >= 0 then @months else 12 + @months end
select @days = DATEPART(dy,@date2) - DATEPART(dy,@date1)
select @days = case when @days >=0 then @days else DATEDIFF(DAY,DATEADD(month,-1,@date2),@date2)+@days end
select @years as [@years], @months as [@months], @days as [@days]
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
January 17, 2012 at 11:14 am
i have tehse saved in my snippets; does this do what you want?
--elapsed time/datediff
select
[Days] = datediff(day,0,ET-ST)-1,
[Hours] = datepart(Hour,ET-ST)-1,
[Minutes] = datepart(Minute,ET-ST)-1,
[Seconds] = datepart(Second,ET-ST),
[Milliseconds] = datepart(millisecond,ET-ST)
from
(
select -- Test Data
ST = convert(datetime,'2008/09/22 00:35:33.997'),
ET = convert(datetime,'2009/10/22 04:05:45.443')
) a
select [Years] = datediff(year,0,ET-ST)-1,
[Months] = datepart(month,ET-ST)-1,
[Days] = datepart(day,ET-ST)-1,
[Hours] = datepart(Hour,ET-ST),
[Minutes] = datepart(Minute,ET-ST),
[Seconds] = datepart(Second,ET-ST),
[Milliseconds] = datepart(millisecond,ET-ST)
from
(
select -- Test Data
ST = convert(datetime,'2008/09/22 00:35:33.997'),
ET = convert(datetime,'2009/10/23 04:05:45.443')
) a
Lowell
January 17, 2012 at 8:48 pm
select [Years] = datediff(year,0,ET-ST)-1,
[Months] = datepart(month,ET-ST)-1,
[Days] = datepart(day,ET-ST)-1
from
(
select -- Test Data
ST = convert(datetime,'2011/01/01'),
ET = convert(datetime,'2013/01/13')
) a
Gives 1 year, 0 months and 13 days.
January 17, 2012 at 8:54 pm
declare @date1 date = '2010-05-17'
declare @date2 date = '2013-01-16'
declare @years int
declare @months int
declare @days int
select @years = DATEDIFF(YEAR,@date1,@date2) - case when datepart(day,@date1) > datepart(day,@date2) then 1 else 0 end
select @months = DATEdiff(month,@date1,@date2) % 12 - case when datepart(day,@date1) > datepart(day,@date2) then 1 else 0 end
select @months = case when @months >= 0 then @months else 12 + @months end
select @days = DATEPART(dy,@date2) - DATEPART(dy,@date1)
select @days = case when @days >=0 then @days else DATEDIFF(DAY,DATEADD(month,-1,@date2),@date2)+@days end
select @years as [@years], @months as [@months], @days as [@days]
Gives 2 years, 7 months, -90 days
January 19, 2012 at 6:15 am
Ummm.... how long is a month for this particular problem?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 19, 2012 at 6:31 am
If there's no way for dynamical months, then 30 days/month.
January 19, 2012 at 7:41 am
I think this will give you what you are looking for. It depends on the fact that the variables are set in left to right order. You can separate them out to single SET or SELECT statements if you're not comfortable with that.
DECLARE @FromDate DateTime, @ToDate DateTime
SELECT @FromDate = '2011-01-16', @ToDate = '2013-01-01'
DECLARE @RefDate datetime
,@dayDiff Int
,@monthDiff Int
,@yearDiff Int
IF @FromDate > @ToDate
SELECT @RefDate = @FromDate, @FromDate = @ToDate, @ToDate = @RefDate
SELECT @RefDate = DateAdd(Year, DateDiff(Year, @FromDate, @ToDate), @FromDate)
,@RefDate = CASE WHEN @RefDate > @ToDate THEN DateAdd(Year, -1, @RefDate) ELSE @RefDate END
,@yearDiff = DateDiff(Year, @FromDate, @RefDate)
,@FromDate = @RefDate
,@RefDate = DateAdd(Month, DateDiff(Month, @FromDate, @ToDate), @FromDate)
,@RefDate = CASE WHEN @RefDate > @ToDate THEN DateAdd(Month, -1, @RefDate) ELSE @RefDate END
,@monthDiff = DateDiff(Month, @FromDate, @RefDate)
,@FromDate = @RefDate
,@RefDate = DateAdd(Day, DateDiff(Day, @FromDate, @ToDate), @FromDate)
,@RefDate = CASE WHEN @RefDate > @ToDate THEN DateAdd(Day, -1, @RefDate) ELSE @RefDate END
,@dayDiff = DateDiff(Day, @FromDate, @RefDate)
SELECT @yearDiff, @monthDiff, @dayDiff
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 19, 2012 at 7:52 am
Lowell (1/17/2012)
i have tehse saved in my snippets; does this do what you want?--elapsed time/datediff
select [Years] = datediff(year,0,ET-ST)-1,
[Months] = datepart(month,ET-ST)-1,
[Days] = datepart(day,ET-ST)-1,
[Hours] = datepart(Hour,ET-ST),
[Minutes] = datepart(Minute,ET-ST),
[Seconds] = datepart(Second,ET-ST),
[Milliseconds] = datepart(millisecond,ET-ST)
from
(
select -- Test Data
ST = convert(datetime,'2008/09/22 00:35:33.997'),
ET = convert(datetime,'2009/10/23 04:05:45.443')
) a
This doesn't work as expected, because it doesn't appropriately account for leap years. For example, the range 2011-01-01 to 2012-12-31 contains a leap year (2012). Using ET-ST essentially converts the range to 1900-01-01 to 1902-01-01 which does NOT contain a leap year (1900 though divisible by 4 is not a leap year, because it is not divisible by 400) and subsequently gives the wrong values for year, month, and day.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 19, 2012 at 7:56 am
thanks drew;
it's actually inaccurate in a lot of other situations too, depending on the range between the two dates; that date -1 stuff works just for the date supplied in the example; start putting in other dates and it blows up.
your version is much more elegant, and I added it to my snippets.
Thanks!
Lowell
January 19, 2012 at 6:51 pm
That's why I asked the question about how long a month was. It won't necessarily be accurate for 90 days with different start dates. Even 30 days is a poor estimate.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 20, 2012 at 11:06 am
I think we use the day of the month as the "anniversary", and crossing an anniversary increments the year or the month. For example, if I was married on Jan 10th, every year on Jan 10th I increment the number of years I have been married, regardless of whether the time between the dates had 365 or 366 days. Similarly, when something happens on the 10th day of a month, the next 10th day is the one-month anniversay. If I started dating my wife on Jan 10th, Feb 10th is our one-month anniversary and Mar 10th is our two-month anniversary, despite the fact that the number of days between those intervals does not match.
I suggest that when the day and month of the end date equals or passes the day and month of the start year, then the year increments, and when the day of the end month equals or passes the day of the start month then the month increments.
Therefore, the difference between Feb 28 of one year and Mar 29 of the following year is always 1 year, 1 month, 1 day, whether or not the time included a leap day. Feb 28 to Feb 28 increments the year. 28 to 28 increments the month.
January 20, 2012 at 1:13 pm
Fahey's logic above is what I used in coding my response, since that is how ages are typically calculated and age was specifically mentioned.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 20, 2012 at 2:06 pm
Age Function F_AGE_YYYY_MM_DD
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62729
This function calculates age in years, months and days from @START_DATE through @END_DATE and returns the age in format YYYY MM DD.
Years is the number of full years between @START_DATE and @END_DATE.
Months is the number of full months since the last full year anniversary.
Days is the number of days since the last full month anniversary.
October 29, 2012 at 8:55 am
Try This.
DECLARE @date1 DATETIME,
@date2 DATETIME,
@year INT,
@month INT,
@days INT,
@hours INT,
@min-2 INT,
@sec INT
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)
SELECT CASE WHEN @year = 0 THEN '' ELSE CAST (@year AS VARCHAR)+'Y ' END+
CASE WHEN @month = 0 THEN '' ELSE CAST (@month AS VARCHAR)+'M ' END+
CASE WHEN @days = 0 THEN '' ELSE CAST (@days AS VARCHAR)+'D ' END+
CASE WHEN @hours = 0 THEN '' ELSE CAST (@hours AS VARCHAR)+'H ' END+
CASE WHEN @min-2 = 0 THEN '' ELSE CAST (@min AS VARCHAR)+'Mi ' END+
CASE WHEN @sec = 0 THEN '' ELSE CAST (@sec AS VARCHAR)+'Sec ' END
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply