Re: [PHP-DB] Using CURDATE, DATE_SUB and DATE_ADD
On Monday 10 February 2003 06:57, Barrie Matthews wrote:
> I am using the query below in attempting to control access to a web site.
> The idea is that today's date must be between 1 month before the start date
> and 1 month after the finish date.
>
> $query1 = "SELECT VFT_name FROM `vft` WHERE CURDATE() >
> DATE_SUB(VFT_date_start, INTERVAL 1 month) AND CURDATE() <
> DATE_ADD(VFT_date_finish, INTERVAL 1 month) ";
>
> It seems that if a start date is say March 31, then any date in February
> (even Feb 01) is going to return true. I tried using days (28, 29, .. 35
> etc) instead of 1 month in the query but that doesn't seem to work.
>
> For a start-date of March 11 how can I deny access on Feb 10 but allow
> access starting on Feb 11?
There doesn't seem to be anything wrong with your DATE_SUB() and DATE_ADD():
mysql> select date_sub('2003-03-11', interval 1 month);
+--+
| date_sub('2003-03-11', interval 1 month) |
+--+
| 2003-02-11 |
+--+
1 row in set (0.09 sec)
mysql> select date_sub('2003-03-31', interval 1 month);
+--+
| date_sub('2003-03-31', interval 1 month) |
+--+
| 2003-02-28 |
+--+
1 row in set (0.00 sec)
You should check that VFT_date_start and VFT_date_finish actually contain what
you expect them to contain. And also you could consider using BETWEEN instead
of the less-than and greater-than comparisons.
--
Jason Wong -> Gremlins Associates -> www.gremlins.biz
Open Source Software Systems Integrators
* Web Design & Hosting * Internet & Intranet Applications Development *
--
Search the list archives before you post
http://marc.theaimsgroup.com/?l=php-db
--
/*
Tell a man there are 300 billion stars in the universe and he'll believe you.
Tell him a bench has wet paint on it and he'll have to touch to be sure.
*/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Using CURDATE, DATE_SUB and DATE_ADD
I am using the query below in attempting to control access to a web site. The idea is that today's date must be between 1 month before the start date and 1 month after the finish date. $query1 = "SELECT VFT_name FROM `vft` WHERE CURDATE() > DATE_SUB(VFT_date_start, INTERVAL 1 month) AND CURDATE() < DATE_ADD(VFT_date_finish, INTERVAL 1 month) "; It seems that if a start date is say March 31, then any date in February (even Feb 01) is going to return true. I tried using days (28, 29, .. 35 etc) instead of 1 month in the query but that doesn't seem to work. For a start-date of March 11 how can I deny access on Feb 10 but allow access starting on Feb 11? Thanks for any help. Barrie --- Barrie Matthews, Webmaster Heurisko Ltd, Learning Technology Specialists Spark House, 5 Durham St, PO Box 8577, Christchurch, New Zealand P +64 3 353-7360F +64 3 366-5488 [EMAIL PROTECTED]www.heurisko.co.nz Heurisko services: - LEARNZ, NZ's award winning online education programme www.learnz.org.nz/ - TheSchoolDaily - daily education news and features www.theschooldaily.com/ - NZ agent for TextHELP! products - award winning learning disability software www.texthelp.co.nz/ - Suppliers of digital cameras, scanners, printers and POLYCOMS www.heurisko.co.nz/teachingtools/ - Web server hosting and internet/intranet solutions www.heurisko.co.nz/webservices/ --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
