thx's Keith, another option :)
Mike
----- Original Message -----
From: <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
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 <[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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
