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 <[EMAIL PROTECTED]>,
> Jorrit Kronjee <[EMAIL PROTECTED]>, mysql@lists.mysql.com
> From: Rhino <[EMAIL PROTECTED]>
> Subject: Re: Getting number days between 2 dates
>
>
> ----- Original Message ----- From: "Mike Blezien"
> <[EMAIL PROTECTED]>
> To: "Jorrit Kronjee" <[EMAIL PROTECTED]>; <mysql@lists.mysql.com>
> Sent: Saturday, April 01, 2006 9:00 AM
> Subject: Re: Getting number days between 2 dates
>
>
> > Jorrit,
> >
> > ----- Original Message ----- From: "Jorrit Kronjee"
> > <[EMAIL PROTECTED]>
> > To: <mysql@lists.mysql.com>
> > 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/[EMAIL PROTECTED]
