#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    7
    Rep Power
    0

    SQL syntax to display values within certain date range


    Hi,

    I am trying to learn SQL at the moment and would appreciate some help with specifying the date using SQL syntax. Currently I have my table in Microsoft Access but I am using the SQL query design field to enter SQL code to run queries.

    One of the columns in my table is the OrderDate column, which specifies the date an order was made in the format MM/DD/YYYY. I want to write a query to only display data where the OrderDate is between 1st Jan 2007 and 31st Dec 2007 however I am having issues. I've tried a few ways but none have worked. I am beginning to think whether its related to some formatting of the field in the table which I need to specify. My code is listed below. Thanks for the help in advance.

    Code 1:

    SELECT *
    FROM OrderTable
    WHERE OrderDate BETWEEN 01/01/2007 AND 12/31/2007;

    Result shown is incorrect and all values other than 2007 is shown. If I put ' ' around the dates ('01/01/2007' AND '12/31/2007'), I get an error 'Data type mismatch in criteria expression.'

    Code 2:

    SELECT *
    FROM OrderTable
    WHERE OrderDate >= 01/01/2007 AND OrderDate < 12/31/2007;

    Result shown is incorrect again and only values other than 2007 are shown. If I put the ' ' around the dates, same error is encountered as Code 1 above.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    WHERE OrderDate BETWEEN #2007-01-01# AND #2007-12-31#
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    7
    Rep Power
    0
    Thanks. I have tried your advice and the output is now showing everything including the 2007 fields plus the fields outside of year 2007. Unfortunately it did not work since I just want to see the entries for 2007.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    could you please post a description of your table, along with a couple of rows of data

    i'm curious whether you're actually using a DATE column type
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    7
    Rep Power
    0
    Thanks for the quick reply. The table contains information about customers who have placed orders. It contains Order number, order date, customer number, employee number, order name, order street, order city, order state, and order zip. The order date is specified as MM/DD/YYYY. One thing i have noticed is that, there is a small plus side before the first column and when i click it for each row the product number and quantity columns are shown. I have pasted some sample values in the next post below.

    I am not sure how to specify the date type since I am new to this, but I have downloaded the database example from the below site if you would like to try.

    highered.mcgraw-hill.com/sites/0072942207/student_view0/chapter4/databases.html

    File is located in the zip file and is called the OrderEntry:
    Chap04Student\Chap04\OrdEntryChpt4A2000NoSolution

    I am working on the OrderTbl and trying to extract results based on the OrdDate column.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    7
    Rep Power
    0
    OrdNo OrdDate CustNo EmpNo OrdName OrdStreet OrdCity OrdState OrdZip
    O1116324 01/23/2007 C0954327 E8544399 Sheri Gordon 336 Hill St. Littleton CO 80129-5543
    O1231231 01/23/2007 C9432910 E9954302 Larry Styles 9825 S. Crest Lane Bellevue WA 98104-2211
    O1241518 02/10/2007 C9549302 Todd Hayes 1400 NW 88th Lynnwood WA 98036-2244
    O1455122 01/09/2007 C8574932 E9345771 Wally Jones 411 Webber Ave. Seattle WA 98105-1093
    O1579999 01/05/2007 C9543029 E8544399 Tom Johnson 1632 Ocean Dr. Des Moines WA 98222-1123
    O1615141 01/23/2007 C8654390 E8544399 Candy Kendall 456 Pine St. Seattle WA 98105-3345
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    sorry, man, i can't read zip files

    could you take a screenshot of the design view of the table please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    7
    Rep Power
    0
    Actually your method is correct and works. I was confused because i am seeing this last entry from the table which is for year 2013 appearing in the results when i run the query. All the fields for this entry are empty except the order date column but it has the asterisk next to the first column so it is a new row but the date has to be entered i assume as a per-requisite.

    Thanks a lot for the help.

IMN logo majestic logo threadwatch logo seochat tools logo