SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru davedibiase's Avatar
    Join Date
    Aug 2001
    Location
    Toronto, Canada
    Posts
    829
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Using MySQL for selecting a date range.

    Hi,

    I was wondering if anyone knew how to search for a range of dates within a table. I have a field set as "date" with default input of "0000-00-00". I was wondering if SQL had a neat function to select a range of date values from specific times then list them in DESC order.

    Thanks for your help.
    ||Dave Di Biase||
    ----------------------------------
    "There are 2 secrets in life. 1) Never say everything you know."
    GFXWARS - The ultimate graphics battle!

  2. #2
    SitePoint Addict melchiorus's Avatar
    Join Date
    Jun 2004
    Location
    Indiana
    Posts
    283
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT * FROM table WHERE date < 0000-00-00 AND date > 0000-00-00 DESC
    Might not be 100% correct though.
    -Melchior (Stephen Craton)

  3. #3
    SitePoint Guru davedibiase's Avatar
    Join Date
    Aug 2001
    Location
    Toronto, Canada
    Posts
    829
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I will test it when I get a chance. Pretty simple solution. lol I should have thought of it -_- :-p

    thanks.
    ||Dave Di Biase||
    ----------------------------------
    "There are 2 secrets in life. 1) Never say everything you know."
    GFXWARS - The ultimate graphics battle!

  4. #4
    SitePoint Addict silent's Avatar
    Join Date
    Jun 2004
    Location
    Roaming North America
    Posts
    220
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by melchiorus
    Code:
    SELECT * FROM table WHERE date < 0000-00-00 AND date > 0000-00-00 DESC
    Might not be 100% correct though.
    1. Dates must be encapsulated in quotes. The above statement won't give you an error, but it won't give you the answer you want. (Actually, it will give you an error, considering the DESC is just left stranded on the end of the statement without an ORDER BY clause with a field...)

    2. Second, you can use the BETWEEN operator on dates, e.g. for an sample Account table with a CreatedOn date field:
    Code:
    SELECT COUNT(*) FROM Account WHERE CreatedOn BETWEEN '2004-04-05' AND '2004-06-21'
    3. You can use the date comparison functions in MySQL, e.g.:
    Code:
    SELECT COUNT(*) FROM Account WHERE CreatedOn >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)
    gives you all accounts created in the past month.

    HTH

  5. #5
    SitePoint Guru davedibiase's Avatar
    Join Date
    Aug 2001
    Location
    Toronto, Canada
    Posts
    829
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, I worked out all the errors with the line and it works perfectly fine the way he suggested. Its also much easier than your solution.

    Thanks.
    ||Dave Di Biase||
    ----------------------------------
    "There are 2 secrets in life. 1) Never say everything you know."
    GFXWARS - The ultimate graphics battle!

  6. #6
    SitePoint Addict silent's Avatar
    Join Date
    Jun 2004
    Location
    Roaming North America
    Posts
    220
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by davedibiase
    Yes, I worked out all the errors with the line and it works perfectly fine the way he suggested. Its also much easier than your solution.

    Thanks.
    Fair enough, that's your opinion, though be aware that:

    Code:
    ...WHERE dateField BETWEEN '2004-01-01' AND '2004-01-05'
    is different from:

    Code:
    ...WHERE dateField > '2004-01-01' AND dateField < '2004-01-05'
    One is inclusive, the other exclusive.

  7. #7
    SitePoint Guru davedibiase's Avatar
    Join Date
    Aug 2001
    Location
    Toronto, Canada
    Posts
    829
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Uh huh.

    I used >= to fix that problem.

    Thanks for your input.
    ||Dave Di Biase||
    ----------------------------------
    "There are 2 secrets in life. 1) Never say everything you know."
    GFXWARS - The ultimate graphics battle!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •