Re: Using CURDATE, DATE_SUB and DATE_ADD

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux