Trouble with DATE

[Cecass]

My MSQL query returns the number of days between two dates How can I convert this into Years,Months,Days format?

Help please.

 

[gmmastros]

I created a user defined function for this purpose. Here it is.

ALTER Function dbo.CalculateAge
(
@BirthDate As DateTime,
@Now as DateTime
)
Returns VarChar(100)
AS
Begin
--dateformat= yyyymmdd
declare @from datetime
declare @to datetime

set @from = Convert(VarChar(4), Year(@BirthDate))
+ Right('00' + Convert(VarChar(2), Month(@BirthDate)), 2)
+ Right('00' + Convert(VarChar(2), Day(@BirthDate)), 2)

set @to = Convert(VarChar(4), Year(@Now))
+ Right('00' + Convert(VarChar(2), Month(@Now)), 2)
+ Right('00' + Convert(VarChar(2), Day(@Now)), 2)

declare @year int set @year=0
declare @month int set @month=0
declare @day int set @day=0
declare @rndate datetime
set @rndate=@from --rndate = runningdate

--calculate the @year
if(datepart(year,@to)>datepart(year,@from))
begin
set @year = datediff(year,@from,@to)
set @rndate = dateadd(year,@year,@from) --update runningdate
if(@rndate>@to)
begin
set @year = datediff(year,@from,dateadd(year,-1,@to)) --calculate years from @from to @to - 1 year
set @rndate = dateadd(year,@year,@from) --update runningdate
end

end

--add 1 month as long as running date is smaller than or equal to @to
while @rndate<=@to
begin
set @rndate = dateadd(month,1,@rndate)
if (@rndate<=@to)
begin
set @month=@month+1
end
end
--set @rndate back 1 month
set @rndate=dateadd(month,-1,@rndate)

--start to count days
while @rndate<@to
begin
set @rndate=dateadd(day,1,@rndate)
set @day=@day+1
end

Return Convert(VarChar(5), @year) + ' Years, ' + Convert(VarChar(5), @Month) + ' Months, ' + Convert(VarChar(5), @day) + ' Days'
End

 

[gmmastros]

I apologize. I did not create this function. I found it somewhere on the internet a long time ago.

 

[donutman]

Don't use a UDF for something like that. Is this good enough or do you have a need that dictates the use of actual days in each month?

[Blue]DECLARE[/Blue] @Date1 [Blue]datetime[/Blue]
[Blue]DECLARE[/Blue] @Date2 [Blue]datetime[/Blue]
[Blue]SET[/Blue] @Date1[Gray]=[/Gray][Fuchsia]GetDate[/Fuchsia][Gray]([/Gray][Gray])[/Gray][Gray]-[/Gray]359
[Blue]SET[/Blue] @Date2[Gray]=[/Gray][Fuchsia]GetDate[/Fuchsia][Gray]([/Gray][Gray])[/Gray]
[Blue]SELECT[/Blue] [Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray][Blue]dd[/Blue][Gray],[/Gray]@Date1[Gray],[/Gray]@Date2[Gray])[/Gray]/360[Gray],[/Gray] 
       [Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray][Blue]dd[/Blue][Gray],[/Gray]@Date1[Gray],[/Gray]@Date2[Gray])[/Gray][Gray]%[/Gray]360/30[Gray],[/Gray]
       [Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray][Blue]dd[/Blue][Gray],[/Gray]@Date1[Gray],[/Gray]@Date2[Gray])[/Gray][Gray]%[/Gray]30

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[gmmastros]

Maybe I'm missing something here.

When I Use @Date1='3/23/1970' (my birthday), your select string tells me that I am 35 years, 5 months, and 5 days old.

My UDF returns... 34 Years, 10 Months, 30 Days

 

[donutman]

You missed my 2nd sentence.

Is this good enough or do you have a need that dictates the use of actual days in each month?

How you write the query depends on your needs. What does your UDF return when you are born on 2/29 of a leap year and you evaluate it on 2/28/2005 and 3/1/2005? If the business rules get really nasty what do you do when your date and time of birth is recorded in UK and you evaluate it in USA? It's not a facetious point...it all depends.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[gmmastros]

donutman, I didn't intend to offend. Please accept my apology.

I would like to clarify that I did not write the UDF. I found it a while ago, and I thought it might help cecass.

I tested the UDF like you suggested.

2004 was a leap year, so....

difference between 2/29/2004 and 2/28/2005 displays 1 Year, 0 Months, 0 Days

Difference between 2/29/2004 and 3/1/2005 displays 1 Year, 0 Months, 1 days

 

[vongrunt]

Problem with displaying date difference as (yet another) date is conceptual. 31 day sometimes equals 1 month, sometimes 1 month and 3 days. So it may serve for "human readable" purposes at best.

And yes, calendar year has a little bit more than 360 days .

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[donutman]

I too didn't mean to offend by suggesting that your UDF was bad. I try to find solutions that don't involve the use of a UDF as they cause a performance hit. Computing the difference between dates is rarely a situation where a UDF is required, but if the business rules are complex enough it might be worthwhile to take the performance hit in the interest of making it clear how the calculation is being performed.
I shouldn't have asked you to try and make the UDF fail, so I appologize (and it serves me right that it didn't). I sense that I've crossed over some line in the last couple of days...I'll watch my step!
-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[gmmastros]

Now that we are all touchy feely... I'd like to point out that my birthday is only a month (approximately) away. I expect cards from everyone. :->

 

[donutman]

[Blue]DECLARE[/Blue] @Date1 [Blue]datetime[/Blue]
[Blue]DECLARE[/Blue] @Date2 [Blue]datetime[/Blue]
[Blue]SET[/Blue] @Date1[Gray]=[/Gray][red]'3/23/1970'[/red] 
[Blue]SET[/Blue] @Date2[Gray]=[/Gray][Fuchsia]GetDate[/Fuchsia][Gray]([/Gray][Gray])[/Gray]
[Blue]SELECT[/Blue] [Blue]CASE[/Blue] [Blue]WHEN[/Blue] [Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]yy[Gray],[/Gray][Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]yy[Gray],[/Gray]@Date1[Gray],[/Gray]@Date2[Gray])[/Gray][Gray],[/Gray]@Date1[Gray])[/Gray][Gray]>[/Gray]@Date2 [Blue]THEN[/Blue] [Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]yy[Gray],[/Gray]@Date1[Gray],[/Gray]@Date2[Gray])[/Gray][Gray]-[/Gray]1 [Blue]ELSE[/Blue] [Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]yy[Gray],[/Gray]@Date1[Gray],[/Gray]@Date2[Gray])[/Gray] [Blue]END[/Blue][Gray],[/Gray]
       [Blue]CASE[/Blue] [Blue]WHEN[/Blue] [Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]mm[Gray],[/Gray][Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]mm[Gray],[/Gray]@Date1[Gray],[/Gray]@Date2[Gray])[/Gray][Gray],[/Gray]@Date1[Gray])[/Gray][Gray]>[/Gray]@Date2 [Blue]THEN[/Blue] [Gray]([/Gray][Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]mm[Gray],[/Gray]@Date1[Gray],[/Gray]@Date2[Gray])[/Gray][Gray]-[/Gray]1[Gray])[/Gray][Gray]%[/Gray]12 [Blue]ELSE[/Blue] [Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]mm[Gray],[/Gray]@Date1[Gray],[/Gray]@Date2[Gray])[/Gray][Gray]%[/Gray]12 [Blue]END[/Blue][Gray],[/Gray]
       [Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray][Blue]dd[/Blue][Gray],[/Gray][Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]mm[Gray],[/Gray][Blue]CASE[/Blue] [Blue]WHEN[/Blue] [Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]mm[Gray],[/Gray][Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]mm[Gray],[/Gray]@Date1[Gray],[/Gray]@Date2[Gray])[/Gray][Gray],[/Gray]@Date1[Gray])[/Gray][Gray]>[/Gray]@Date2 [Blue]THEN[/Blue] [Gray]([/Gray][Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]mm[Gray],[/Gray]@Date1[Gray],[/Gray]@Date2[Gray])[/Gray][Gray]-[/Gray]1[Gray])[/Gray][Gray]%[/Gray]12 [Blue]ELSE[/Blue] [Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]mm[Gray],[/Gray]@Date1[Gray],[/Gray]@Date2[Gray])[/Gray][Gray]%[/Gray]12 [Blue]END[/Blue] [Gray],[/Gray][Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]yy[Gray],[/Gray][Blue]CASE[/Blue] [Blue]WHEN[/Blue] [Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray]yy[Gray],[/Gray][Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]yy[Gray],[/Gray]@Date1[Gray],[/Gray]@Date2[Gray])[/Gray][Gray],[/Gray]@Date1[Gray])[/Gray][Gray]>[/Gray]@Date2 [Blue]THEN[/Blue] [Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]yy[Gray],[/Gray]@Date1[Gray],[/Gray]@Date2[Gray])[/Gray][Gray]-[/Gray]1 [Blue]ELSE[/Blue] [Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]yy[Gray],[/Gray]@Date1[Gray],[/Gray]@Date2[Gray])[/Gray][Blue]END[/Blue][Gray],[/Gray]@Date1[Gray])[/Gray][Gray])[/Gray] [Gray],[/Gray]@Date2[Gray])[/Gray]

So maybe this becomes one of those situations where you say to h.. with performance, use a UDF? I can sense it now ESquared is going to come around and show how this counts a full day for those born right before midnight.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

Sucking me in like this is mean! I'm out of time for now, though.

 

[ESquared]

Karl,

I have to say this is a thorny problem and your query seems superior to any of mine so far.

I chose to say that one has already "had" one's birthday on the day itself, even if the date and time haven't been reached yet. For example, if you were born at 12 noon, you're still one year older at 8am on your birthday. But other than that, I'm still having problems with birthdates whose day portion is greater than the number of days in the previous month from now.

 

[ESquared]

AHA!

Take THIS you mean person you:

SELECT
  Date1,
  Date2,
  Years = Y2 - Y1 -
    CASE WHEN M1 > M2 OR (M1=M2 AND D1 > D2) THEN 1 ELSE 0 END,
  Months = (M2 - M1 +
    CASE WHEN D1 > D2 THEN 11 ELSE 12 END
    ) % 12,
  Days = D2 - D1 + 
    CASE WHEN D1 > D2 THEN
       CASE WHEN D1 > D2PM THEN D1 ELSE D2PM END
    ELSE 0 END
FROM
  (
  SELECT
    Date1,
    Date2,
    Y1 = DatePart(yy, Date1),
    Y2 = Datepart(yy, Date2),    
    M1 = DatePart(m, Date1),
    M2 = Datepart(m, Date2),
    D1 = DatePart(d, Date1),
    D2 = Datepart(d, Date2),
    D2PM = DatePart(d, DateAdd(m, DateDiff(m, 0, Date2), -1))
  FROM
    #BDates
  ) Calc

CREATE TABLE #BDates (Date1 datetime, Date2 datetime)
INSERT #BDates
SELECT '1965-07-29 07:47:57.233', '2005-02-28 15:16:20.907' UNION
SELECT '1972-08-31 03:09:13.227', '2008-06-30 17:39:45.430' UNION
SELECT '1984-02-29 00:42:23.113', '2005-10-01 01:58:43.550'

Compare my query's results to yours to see some differences in how we handle various dates.

 

[ESquared]

Also, you can simplify your query some by moving parts out of the CASE statement:

SELECT
dYears = 
  DateDiff(yy, Date1, Date2) - 
  CASE WHEN DateAdd(yy, DateDiff(yy, Date1, Date2), Date1) > Date2 THEN 1 ELSE 0 END,
dMonths = (
    DateDiff(mm, Date1, Date2) -
    CASE WHEN DateAdd(mm, DateDiff(mm, Date1, Date2), Date1) > Date2 THEN 1 ELSE 0 END 
  ) % 12,
dDays = 
  DateDiff(dd,
    DateAdd(mm, (
      DateDiff(mm, Date1, Date2)
      - CASE WHEN DateAdd(mm, DateDiff(mm, Date1, Date2), Date1) > Date2 THEN 1 ELSE 0 END
      ) % 12,
      DateAdd(yy,
        DateDiff(yy, Date1, Date2) - 
        CASE WHEN DateAdd(yy, DateDiff(yy, Date1, Date2), Date1) > Date2 THEN 1 ELSE 0 END
        , Date1
      )
    ) , Date2
  )
FROM #BDates

And now that I think about it, you can do a derived table, too, to simplify:

SELECT
  dYears = Years,
  dMonths = Months,
  dDays = DateDiff(dd, DateAdd(mm, Months, DateAdd(yy, Years, Date1)), Date2)
FROM (
  SELECT
    Date1, Date2,
    Years = DateDiff(yy, Date1, Date2)
      - CASE WHEN DateAdd(yy, DateDiff(yy, Date1, Date2), Date1) > Date2 THEN 1 ELSE 0 END,
    Months = (
      DateDiff(mm, Date1, Date2)
      - CASE WHEN DateAdd(mm, DateDiff(mm, Date1, Date2), Date1) > Date2 THEN 1 ELSE 0 END 
	 ) % 12
  FROM #BDates
)

And now that I think about it again, your construction is simpler than mine

DateDiff(yy, Date1, Date2)

=

Datepart(yy, Date2) - DatePart(yy, Date1)

They're, um, basically the same query.

 

[Cecass]

I did not mean to cause trouble! But thanks for the info.

 

[donutman]

This is called fun, not trouble.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[gmmastros]

I've been having fun too. Here's what I did....

I created a table with over 1 million dates in it. Then I created a stored procedure that would return the duration using the UDF method. And then return the duration using the donutman method. Of course, I played and played.

The best part of playing was to convert donutman's select query in to a UDF. (insert screaming here)

Here's what I found....

Donutman's select query took 11 Seconds.
My Original UDF took 70 seconds.
Donutman's Select query converted to UDF took 37 seconds.

So... Here's what I think. My original UDF has 2 while loops. Those loops probably account for the difference (in time) between the original UDF and donutman's converted UDF.

I can't seem to wrap my brain around the difference between donutman's select query and donutman's converted UDF. The only conclusion I can come up with is, "Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil!"

My hat's off to you donutman. You've shown me the light.

With Much Respect,
George

 

[donutman]

Thanks. It's not hard to test the performance hit that UDFs cause. Create one that just returns the input parameter. As I recall you take a 20% hit.
While you have that test table, check out ESquared's derived table etc. How's it stack up?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[gmmastros]

donutman, I added ESquared's method in to the mix. Here's what I get.

donutman's select 11 seconds.
Original UDF 70 seconds.
donutman's converted UDF 21 seconds.
ESquared's select 6 seconds.