MySQL ODBC Connector - Getting number days between 2 dates

Author

Getting number days between 2 dates

Mike Blezien

2006-04-01, 7:25 am

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(mickalo)Blezien

====================
===========
Thunder Rain Internet Publishing
Providing Internet Solution that Work
====================
===========

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Jorrit Kronjee

2006-04-01, 9:28 am

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(mickalo)Blezien

> ====================
===========
> Thunder Rain Internet Publishing
> Providing Internet Solution that Work
> ====================
===========
>

Mike,

You probably want to use something like this:

SELECT TO_DAYS('2006-03-15') - TO_DAYS('2006-03-01');

Jorrit

--
System Developer

Infopact Network Solutions
Hoogvlietsekerkweg 170
3194 AM Rotterdam Hoogvliet
tel. +31 (0)88 - 4636700
fax. +31 (0)88 - 4636799
mob. +31 (0)6 - 14105968
j.kronjee@infopact.nl
http://www.infopact.nl/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Mike Blezien

2006-04-01, 9:28 am

Jorrit,

----- Original Message -----
From: "Jorrit Kronjee" <j.kronjee@infopact.nl>
To: <mysql@lists.mysql.com>
Sent: Saturday, April 01, 2006 7:46 AM
Subject: Re: Getting number days between 2 dates

> Mike Blezien wrote:
[color=darkred]
> 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

Mike

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Jorrit Kronjee

2006-04-01, 9:28 am

Mike Blezien wrote:
> Jorrit,
>
> ----- Original Message ----- From: "Jorrit Kronjee" <j.kronjee@infopact.nl>
> To: <mysql@lists.mysql.com>
> Sent: Saturday, April 01, 2006 7:46 AM
> Subject: Re: Getting number days between 2 dates
>
>
>
>
> Thanks, that works, also using the DAYOFYEAR produces the same results
> as I just found :)
>
> appreciate the help
>
> Mike
>
>

Mike,

DAYOFYEAR works only well if both dates are in the same year.

SELECT DAYOFYEAR('2006-01-01') - DAYOFYEAR('2005-12-31');

results in a negative number.

Jorrit

--
System Developer

Infopact Network Solutions
Hoogvlietsekerkweg 170
3194 AM Rotterdam Hoogvliet
tel. +31 (0)88 - 4636700
fax. +31 (0)88 - 4636799
mob. +31 (0)6 - 14105968
j.kronjee@infopact.nl
http://www.infopact.nl/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Rhino

2006-04-01, 9:28 am

----- Original Message -----
From: "Mike Blezien" <mickalo@frontiernet.net>
To: "Jorrit Kronjee" <j.kronjee@infopact.nl>; <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" <j.kronjee@infopact.nl>
> To: <mysql@lists.mysql.com>
> Sent: Saturday, April 01, 2006 7:46 AM
> Subject: Re: Getting number days between 2 dates
>
>
>
>
> 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

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.3.4/299 - Release Date: 31/03/2006

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

mysql@karsites.net

2006-04-01, 11:26 am

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@frontiernet.net>,
> Jorrit Kronjee <j.kronjee@infopact.nl>, mysql@lists.mysql.com
> From: Rhino <rhino1@sympatico.ca>
> Subject: Re: Getting number days between 2 dates
>
>
> ----- Original Message ----- From: "Mike Blezien"
> <mickalo@frontiernet.net>
> To: "Jorrit Kronjee" <j.kronjee@infopact.nl>; <mysql@lists.mysql.com>
> Sent: Saturday, April 01, 2006 9:00 AM
> Subject: Re: Getting number days between 2 dates
>
>
> 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...sie.nctu.edu.tw

Mike Blezien

2006-04-01, 11:26 am

thx's Keith, another option :)

Mike
----- Original Message -----
From: <mysql@karsites.net>
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:
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql? unsub...br /> ernet.net
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links