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
    Regular Coder
    Join Date
    Jul 2002
    Posts
    301
    Thanks
    7
    Thanked 2 Times in 2 Posts

    Question <= less than or equal, datetime in mysql statement??

    Hi, I'm stuck on what I think is a quirk of MySQL.

    I have a table 'tblTest'
    I have a field in their called 'timestamp' and it is the type 'datetime'. Typical value might be: "2006-05-16 10:46:23"

    I have this SQL query to select orders between certain dates:
    Code:
    SELECT  * 
    FROM  `tblTest` 
    WHERE timestamp >=  '2006/05/01' AND timestamp <=  '2006/05/31';
    This selects records with a timestamp of 2006-05-01 through to 2006-05-30.

    My thinking is that records for 2006-05-31 should also be returned and yet they are not!!! Why is that?

    If I adjust the last date to be 2006/06/01 in includes records on 2006-05-31 but not 2006-06-01. So I guess the equals part of <= isn't working int eh MySQL query and I need to work out why and how to achieve the result I need.

    Can anyone explain this to me please? (PS it makes no difference if I change the / for -)

    Yours hopefully

    Matt
    Last edited by MattyUK; 09-16-2006 at 06:10 PM.

  • #2
    New Coder
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    First don't use reseverd words like timestamp as col names

    and check the between statement from the mysql manual

  • #3
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    if you don't give a full timestamp (i.e. yours are missing a time) then it assumes 00:00:00, i.e the very beginning of the day, so >= will appear to be inclusive, while <= won't

  • #4
    Regular Coder
    Join Date
    Jul 2002
    Posts
    301
    Thanks
    7
    Thanked 2 Times in 2 Posts
    Hi Olaf
    I had no idea timestamp was a reserved word. Can you point me at a list of reserved words please? Thanks for pointing that out. I had tried BETWEEN beforehand as well. Same problem.

    Hi GJay
    Thanks that was it. I was overlooking the default time values it was working with. Code now corrected. Thanks, that was very helpful.

    Cheers all.

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Mysql reserved words. You will note an odd thing in that timestamp isn't on the reserved word list, but it kind of is. MySQL has decided to allow some words to be used because they had previously been used. Timestamp is one of those. but out of habit, since it is a column type you shouldn't also use it as a column name.

  • #6
    Regular Coder
    Join Date
    Jul 2002
    Posts
    301
    Thanks
    7
    Thanked 2 Times in 2 Posts
    Thanks, that makes a lot of sense, it just hadn't occured to me before. thanks for the help everyone.

    Matt


  •  

    Posting Permissions

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