Date Query Problem

[vetteguy69]

I have a MySQL database and was looking for a query to take in 2 form variables (beginning and end date) and based on those dates, return users from the database who's birthday(already in database column) is within that date range. How would I achieve this type of functionality?

 

[phpPete]

SELECT birthday FROM yourTableName WHERE birthday BETWEEN begin_date AND end_date

 

[rycamor]

Read this page:

http://www.mysql.com/doc/D/a/Date_and_time_functions.html.

It contains all the info on MySQL's date and time functions.

FYI, the above query will work, but be careful about relying too much on MySQL for date handling. There is one thing you need to know about MySQL's date handling: it doesn't use FULL date constraints, like most enterprise databases (including PostgreSQL). For example in MySQL, you can save the value '2002-02-31' in a date column. This is not a valid date, however, because February only has 28 (or 29 for Leap Year) days. Also, the date column type allows you to store a 'zero' date, such as '0000-00-00', which is again wrong (The year can be 0000, but there is no 0 day or 0 month possible).

MySQL simply makes it possible for every month to have 0 - 31 days -- which actually amounts to 32 days, and for every year to have up to 13 months (0-12). In short, depending on MySQL for accurate date calculation is not advisable. This is why conversion to Unix timestamp, and date manipulation in your programming environment, such as PHP is the only real answer. (Unless you want to check into PostgreSQL, which has MUCH more advanced methods for handling dates, such as the very cool 'interval' datatype). -------------------------------------------

"Calculus is just the meaningless manipulation of higher symbols"
                          -unknown F student