Re: Getting number days between 2 dates
On Sunday 04 February 2007 17:24:29 Jim MacDiarmid wrote: I've been trying to figure this out using the CURDATE() function, but I keep getting a syntax error. Below is the code I'm using: SELECT `vb_links`.`DateAdded` FROM `vb_links` WHERE DATEDIFF( CURDATE() , `vb_links`.`DateAdded` ); Normally, a WHERE condition says WHERE field = [constant|function()|...] You have WHERE function(). -- Scanned by iCritical. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Getting number days between 2 dates
The original query should not generate a syntax error. I just tried it. However, DATEDIFF(CURDATE(), xxx) will probably evaluate to TRUE for every record so the WHERE clause as written isn't useful. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Duncan Hill [mailto:[EMAIL PROTECTED] Sent: Monday, February 05, 2007 7:33 AM To: mysql@lists.mysql.com Subject: Re: Getting number days between 2 dates On Sunday 04 February 2007 17:24:29 Jim MacDiarmid wrote: I've been trying to figure this out using the CURDATE() function, but I keep getting a syntax error. Below is the code I'm using: SELECT `vb_links`.`DateAdded` FROM `vb_links` WHERE DATEDIFF( CURDATE() , `vb_links`.`DateAdded` ); Normally, a WHERE condition says WHERE field = [constant|function()|...] You have WHERE function(). -- Scanned by iCritical. -- 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]
RE: Getting number days between 2 dates
I've been trying to figure this out using the CURDATE() function, but I keep
getting a syntax error. Below is the code I'm using:
SELECT
`vb_links`.`DateAdded`
FROM
`vb_links`
WHERE DATEDIFF( CURDATE() , `vb_links`.`DateAdded` );
Any thoughts?
-Original Message-
From: Mike Blezien [mailto:[EMAIL PROTECTED]
Sent: Saturday, April 01, 2006 1:25 PM
To: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: Re: Getting number days between 2 dates
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Getting number days between 2 dates
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/[EMAIL PROTECTED]
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(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
[EMAIL PROTECTED]
http://www.infopact.nl/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting number days between 2 dates
Mike Blezien wrote:
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
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
[EMAIL PROTECTED]
http://www.infopact.nl/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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
--
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/[EMAIL PROTECTED]
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]
Re: Getting number days between 2 dates
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]
