Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2010
    Posts
    8

    Unanswered: BETWEEN for Dates

    Hello,

    I'm trying to set a query that selects all the information between 2 specific dates. I just can't for the life of me get it to work would the coding be:

    Code:
    SELECT *
    FROM Orders
    WHERE orderDate BETWEEN '01/11/2010' AND '31/11/2010'
    Any help is greatly appreciated

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    WHERE orderDate BETWEEN #2010-11-01# AND #2010-11-31#

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    And since you're using a non-US date format, which is Access' preference, you might want to take a look at Allen Browne's article on the subject:

    Microsoft Access tips: International Dates in Access



    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Also please note that November has 30 days... :P
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Nov 2010
    Location
    USA
    Posts
    1
    I have a similar situation, except I want the range to be from the first day of the current month to the current date. Can't seem to figure out how to do that. Any suggestions would be appreciated.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by fpete1110F View Post
    ... from the first day of the current month to the current date.
    try this --
    Code:
    WHERE YEAR(orderDate) = YEAR(DATE())
      AND MONTH(orderDate) = MONTH(DATE())
      AND orderDate <= DATE()
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2010
    Posts
    8
    Thank you for the reply everyone, I have one last question how do I go about this:

    Listing the customer name, CD title and rating for the review (or reviews) with the highest rating.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Duppy View Post
    Listing the customer name, CD title and rating for the review (or reviews) with the highest rating.
    please note you did not give us any information at all about the table or tables involved

    presumably it's not the "orders" table in your original post

    so, that leaves us free to speculate, and you will have to work it out yourself from this --
    Code:
    SELECT customer_name
         , cd_title
         , rating
      FROM some_table_or_tables
     WHERE rating = ( SELECT MAX(rating) FROM some_table_or_tables )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2010
    Posts
    8
    Quote Originally Posted by r937 View Post
    please note you did not give us any information at all about the table or tables involved

    presumably it's not the "orders" table in your original post

    so, that leaves us free to speculate, and you will have to work it out yourself from this --
    Code:
    SELECT customer_name
         , cd_title
         , rating
      FROM some_table_or_tables
     WHERE rating = ( SELECT MAX(rating) FROM some_table_or_tables )
    Sorry man I've got:

    CDTitles:
    • catalog number
    • title
    • artist
    • genre
    • label
    • price
    • numberinstock


    Orders:
    • customerID
    • orderID
    • orderDate
    • status
    • vouchervalue
    • invoicedate
    • paid


    OrderLines:
    • orderId
    • catalognumber
    • quantity


    Reviews:
    • reviewID
    • customerID
    • catalognumber
    • reviewdate
    • rating
    • comment
    • invoicedate
    • paid


    Customer:
    • customerID
    • firstname
    • lastname
    • email
    • phone
    • address
    • postcode
    • country


    The bulleted points are what's in the table.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's nice, but it's a bit late

    did you understand my solution?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2010
    Posts
    8
    Quote Originally Posted by r937 View Post
    that's nice, but it's a bit late

    did you understand my solution?
    Nope I couldn't work it out, Excuse me for my knowledge on this I'm trying to learn as much as I could

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Duppy View Post
    Nope I couldn't work it out, Excuse me for my knowledge on this I'm trying to learn as much as I could
    okay, fair enough, i'll walk you through it

    we'll start with a portion of the solution

    can you write a query to find the highest rating?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Nov 2010
    Posts
    8
    I think I might have cracked it, Is this correct:

    Code:
    SELECT firstname, lastname , title, rating
    FROM Customer, CDTitles, Review
     WHERE rating = ( SELECT MAX(rating) FROM Review )

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Duppy View Post
    Is this correct:
    what happened when you tested it?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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