Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Oct 2011
    Posts
    20
    Thanks
    1
    Thanked 0 Times in 0 Posts

    SQL SELECT ing for WHERE only DATE meets in DATETIME field

    Hi guys,

    Have a table with a field storing values in DATETIME.
    Say the below records.
    Date
    2011-11-09 08:07:32
    2011-11-08 08:41:04
    2011-11-01 08:51:53
    2011-11-02 08:52:19
    2011-11-05 08:52:56
    2011-11-04 08:53:09
    2011-11-08 08:53:29
    2011-11-08 08:53:59

    How would I write my sql statement if I need to get the records where only the date matches. Time does not need to be matched. As long as the date is matched I'm happy.

    I tried writing SELECT * FROM TABLE WHERE date='2011-11-08' but no success. Probably overlooking something.

    Your help is greatly appreciated.
    Cheers
    Elabuwa

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,959
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    It won't work because you haven't asked it to.
    Is date your column name? You should name it something that isn't a reserved word (or possible reserved word), but you need to extract the DATE out of this DATETIME field and compare that.
    Code:
    SELECT
      yourcolumn
    FROM
      yourtable
    WHERE 
      DATE(yourdateandtimecolumn) ='2011-11-08'

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    But you *can* do it without renaming the field if you put the field name between back ticks (` which shares keyboard key with ~):

    Code:
    SELECT list, of, fields FROM table WHERE DATE(`date`) = '2011-11-08'
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Question: When is a reserved word not a reserved word?
    Answer: When mysql says so!

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    Quote Originally Posted by guelphdad View Post
    Question: When is a reserved word not a reserved word?
    Answer: When mysql says so!
    No disagreement. But MySQL (and, indeed, all DBs) also say you can use a reserved word as a table or field name if you "escape" it. The escape mechanism differs from DB to DB, but it's always there.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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