List:General Discussion« Previous MessageNext Message »
From:Mike Blezien Date:April 1 2006 5:25pm
Subject:Re: Getting number days between 2 dates
View as plain text  
thx's Keith, another option :)

Mike
----- Original Message ----- 
From: <mysql@stripped>
To: <mysql@stripped>
Sent: Saturday, April 01, 2006 10:52 AM
Subject: Re: Getting number days between 2 dates


> 
> 
> Use  SELECT DATEDIFF('new_date', 'old_date');
> 
> 
> mysql> SELECT DATEDIFF('2006-04-01','2006-04-01');
> +-------------------------------------+
> | DATEDIFF('2006-04-01','2006-04-01') |
> +-------------------------------------+
> |                                   0 |
> +-------------------------------------+
> 1 row in set (0.00 sec)
> 
> mysql> SELECT DATEDIFF('2006-04-01','2007-04-01');
> +-------------------------------------+
> | DATEDIFF('2006-04-01','2007-04-01') |
> +-------------------------------------+
> |                                -365 |
> +-------------------------------------+
> 1 row in set (0.00 sec)
> 
> mysql> SELECT DATEDIFF('2006-04-01','2005-04-01');
> +-------------------------------------+
> | DATEDIFF('2006-04-01','2005-04-01') |
> +-------------------------------------+
> |                                 365 |
> +-------------------------------------+
> 1 row in set (0.00 sec)
> 
> 
> DATEDIFF(expr,expr2)
> 
> DATEDIFF() returns the number of days between the start date 
> expr and the end date expr2. expr and expr2 are date or 
> date-and-time expressions. Only the date parts of the values 
> are used in the calculation.
> 
> mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
>        -> 1
> mysql> SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31');
>        -> -31
> 
> Regards
> 
> Keith
> 
> In theory, theory and practice are the same;
> in practice they are not.
> 
> 
> On Sat, 1 Apr 2006, Rhino wrote:
> 
>> To: Mike Blezien <mickalo@stripped>,
>>     Jorrit Kronjee <j.kronjee@stripped>, mysql@stripped
>> From: Rhino <rhino1@stripped>
>> Subject: Re: Getting number days between 2 dates
>> 
>> 
>> ----- Original Message ----- From: "Mike Blezien"
>> <mickalo@stripped>
>> To: "Jorrit Kronjee" <j.kronjee@stripped>;
> <mysql@stripped>
>> Sent: Saturday, April 01, 2006 9:00 AM
>> Subject: Re: Getting number days between 2 dates
>> 
>> 
>> > Jorrit,
>> > 
>> > ----- Original Message ----- From: "Jorrit Kronjee"
>> > <j.kronjee@stripped>
>> > To: <mysql@stripped>
>> > Sent: Saturday, April 01, 2006 7:46 AM
>> > Subject: Re: Getting number days between 2 dates
>> > 
>> > 
>> > > Mike Blezien wrote:
>> > > > Hello,
>> > > > 
>> > > > I'm sure this is a simple query but haven't come up with a
>> > > > good approach. Need to get the number of days between two
>> > > > dates. IE: today's date: (2006-04-01 - 2006-03-05)
>> > > > need to calculate the number of days between these dates..
>> > > > what is the best query statement to accomplish this?
>> > > > 
>> > > > TIA,
>> > 
>> > > Mike,
>> > > You probably want to use something like this:
>> > > 
>> > > SELECT TO_DAYS('2006-03-15') - TO_DAYS('2006-03-01');
>> > 
>> > Thanks, that works, also using the DAYOFYEAR produces the same
>> > results as I just found :)
>> > 
>> > appreciate the help
>> > 
>> I'd be careful with DAYOFYEAR() if I were you.
>> 
>> DAYOFYEAR() only tells you which day it is within a given year. If you try
>> to use DAYOFYEAR to tell the difference in days between dates that are in
>> different years, you are certainly going to get the wrong answer. For
>> instance, DAYOFYEAR(2006-04-01) - DAYOFYEAR(2005-04-01) gives an answer of
>> 0 days when the correct answer is 365.
>> 
>> A better choice for getting the difference between two dates in days is
>> probably DATEDIFF() or TO_DAYS().
>> 
>> --
>> Rhino 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 
>
Thread
Getting number days between 2 datesMike Blezien1 Apr
  • Re: Getting number days between 2 datesJorrit Kronjee1 Apr
  • Re: Getting number days between 2 datesMike Blezien1 Apr
    • Re: Getting number days between 2 datesJorrit Kronjee1 Apr
  • Re: Getting number days between 2 datesRhino1 Apr
    • Re: Getting number days between 2 datesmysql1 Apr
  • Re: Getting number days between 2 datesMike Blezien1 Apr
    • RE: Getting number days between 2 datesJim MacDiarmid4 Feb
      • Re: Getting number days between 2 datesDuncan Hill5 Feb
        • RE: Getting number days between 2 datesJerry Schwartz5 Feb