#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2000
    Posts
    12
    Rep Power
    0
    I want build and extract data from a mysql table by date. The problem is that my table data needs to be by date range as follows.

    Example: key field is DATE field
    row 1: 02-19-96 to 02-06-97
    row 2: 02-07-97 to 01-27-98
    row 3: 01-28-98 to 02-15-99

    and the input for the query will come from a FORM input (without range), example:
    Enter birthday
    input value could be 05-13-96

    How can I query the table and select the row with the correct range.

    Baffled newbie !

  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    18
    You'll need two date fields in your table, start and end. In your form you should ask for month, day and year in seperate fields to make sure you control what order and format the data is in (make sure the year is 4 digits, possibly use an option box??). In your script (I'll use PHP for an example) you can concatenate the year-month-day fields.

    $search_date=$year.'-'.$month.'-'.$day;

    Then your query would look like:

    select * from table_name where $search_date BETWEEN start_date AND end_date;

    Because the mysql DATE datatype uses the YYYY-MM-DD format, a string comparison (which BETWEEN will use in this instance) will work.
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2000
    Posts
    12
    Rep Power
    0
    Thank you very much !

Similar Threads

  1. saving query result as date
    By mega1 in forum Visual Basic Programming
    Replies: 0
    Last Post: January 28th, 2004, 04:21 AM
  2. fetching records with date range
    By veenasv in forum PostgreSQL Help
    Replies: 2
    Last Post: November 13th, 2003, 02:44 PM
  3. date input....different query (sorry)
    By agmogg in forum PHP Development
    Replies: 1
    Last Post: February 23rd, 2003, 02:52 PM
  4. check if date is within a range
    By daverau in forum PHP Development
    Replies: 8
    Last Post: November 22nd, 2002, 02:23 PM
  5. Calculating a date range when the year changes
    By KarenJ in forum MySQL Help
    Replies: 2
    Last Post: January 12th, 2001, 10:00 PM

IMN logo majestic logo threadwatch logo seochat tools logo