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 5 of 5
  1. #1
    Regular Coder
    Join Date
    Feb 2007
    Posts
    218
    Thanks
    25
    Thanked 1 Time in 1 Post

    trouble finding records between two dates

    I've got a little problem with selecting records between two dates.

    When I visit the w3schools test site at:
    http://www.w3schools.com/sql/trysql....ql_select_join

    And enter this:
    Code:
    SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
    FROM Orders
    INNER JOIN Customers
    ON Orders.CustomerID=Customers.CustomerID
    where customers.country='Germany'  
    order by Orders.Orderdate
    it works perfectly, however when I only want to display records between two given dates it returns nothing, while there should be 10 records.

    Code:
    SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
    FROM Orders
    INNER JOIN Customers
    ON Orders.CustomerID=Customers.CustomerID
    where customers.country='Germany'  and  Orders.Orderdate>'1996/10/01' and Orders.Orderdate<'1996/11/30'
    order by Orders.Orderdate
    How come this sql code returns no records??

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    1996/10/01 isn't a valid datetime datatype format. That indicates either the data you are providing it isn't in the proper format OR Order.orderdate isn't a datetime or comparable type (ie: its a string). If its a string, you cannot reliably use comparisons like this.
    If they are datetime or date types, you can use '1996-10-01' for your format. If they are not, you can create a new property on the table, use the mysql functions to copy in the proper datatype based on the string data (conversions are slow, so if you have a large number of records it could take a very long time), and then drop the existing property and rename the new one.
    From that point, you may update your code to use the proper datatype formats.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • Users who have thanked Fou-Lu for this post:

    docock (10-06-2013)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,198
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    FouLu: While you may be correct in theory, in practice MySQL is not at all fussy about the format used for specifying a date.

    Witness:
    Code:
    mysql> select id from profile where signup between '2013-10-1' and '2013-10-4';
    +-------+
    | id    |
    +-------+
    | 14339 |
    | 14340 |
    +-------+
    2 rows in set (0.17 sec)
    
    mysql> select id from profile where  signup between '2013/10/01' and '2013/10/04';
    +-------+
    | id    |
    +-------+
    | 14339 |
    | 14340 |
    +-------+
    2 rows in set (0.17 sec)
    
    mysql> select id from profile where signup between '20131001' and '2013.10.4';
    +-------+
    | id    |
    +-------+
    | 14339 |
    | 14340 |
    +-------+
    2 rows in set (0.19 sec)
    As you can see there, MySQL accepts / or - or . as delimiters. Or accepts dates with NO DELIMITERS at all. And you can use either 1 or 2 digit months and days, mixed as you want, in fact.
    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.

  • #4
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,092
    Thanks
    2
    Thanked 322 Times in 314 Posts
    Unfortunately the OP found some SQL examples and is apparently (based on the forum section he posted this in) using mysql.

    Either his format in the query doesn't match the SQL server he is using or if he is actually using mysql, he probably has his dates stored in a character data type to match the examples he is following, which needs to be changed to a DATE type and format. The DATE conversion that Old Pendant mentioned only works for values involving a DATE data type or date functions.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #5
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Is it *really* that forgiving?
    I don't recall reading that in the strict options, but I may have missed it. I'm *pretty* sure it doesn't let me do that with strict, but now I'll have to check it when I get home :P
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)


  •  

    Posting Permissions

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