Results 1 to 3 of 3

Thread: Dates

  1. #1
    Join Date
    Jan 2009
    Posts
    124

    Unanswered: Dates

    I'm missing something her in the formatting of datetime. This query does not fail it just does not return rows which I know exist.

    SELECT *
    FROM patients
    WHERE timestamp BETWEEN 2009-11-15 AND 2009-12-16

    thanks

  2. #2
    Join Date
    Sep 2009
    Posts
    44
    Two problems:

    One: It's a bad idea to name a column "timestamp" because that's a reserved word. If you have to use it, quote your identifier.

    Two: MySQL is reading 2009-11-15 as 2009 minus 11 minus 15, or 1983. So your query is really saying:

    Code:
    SELECT * FROM patients
    WHERE timestamp between 1983 AND 1981
    MySQL won't warn you when you make a mistake like this. The correct syntax for a date literal, assuming you're using 5.5.

    Your code will probably want, depending on what date type you're actually using:

    Code:
    SELECT *
    FROM patients
    WHERE `TIMESTAMP` BETWEEN date '2009-11-15' AND date '2009-12-16'

  3. #3
    Join Date
    Jan 2009
    Posts
    124

    Dates

    Just kick me, your right, the quotes make all the difference!

    Thanks

Posting Permissions

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