SQL query to retrieve data using date as where clause

Question

I want to retrieve data from database using the following sql query but this is not working. I am trying to fetch data according to input date. In database the date is in format of 03/03/2013. Is following query correct for the problem.

 public DataSet OrderByDate(string date1, string date2)
        {


            //  string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Amrit\\Desktop\\Database.accdb ;Persist Security Info=False;";
            DataSet dataSet = new DataSet();
            OleDbConnection oleConn = new OleDbConnection(connString);

            try
            {
                oleConn.Open();
                string sql = "SELECT Order.OrderNumber, (Customer.Title +SPACE(2)+ Customer.CustomerName) as [Customer Name], Customer.CustomerEbayname, Customer.EmailAddress, Customer.PhoneNumber, (Customer.Address1 + SPACE(2) + Customer.Address2 + SPACE(2)+ Customer.City + SPACE(2) + Customer.PostCode + SPACE(2) + Customer.Country) as Address,  Order.ItemPurchased, Order.PurchasedDate, Order.TotalPrice FROM Customer INNER JOIN [Order] ON Customer.[CustomerID] = Order.[CustomerID] WHERE [PurchasedDate] BETWEEN #date1# AND #date2#";                    OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sql, oleConn);
                dataAdapter.Fill(dataSet, "Customer");
            }
            catch (Exception ex)
            {
                MessageBox.Show("An exception has been occured\n" + ex.ToString());
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                oleConn.Close();
            }
            if (dataSet.Tables.Count <= 0)
                return null;
            else
                return dataSet;
        }

Don't forget to accept if your question is answered.

Johanna Larsson
– Johanna Larsson

2013-03-03 08:51:51 +00:00
Commented Mar 3, 2013 at 8:51 — Johanna Larsson
– Johanna Larsson, Commented Mar 3, 2013 at 8:51

John Woo · Accepted Answer · 2013-03-03 08:44:01Z

1

since you are using Access, wrap the dates with # not by single quotes.

SELECT  [Order].[Order]Number, 
        (Customer.Title +SPACE(2)+ Customer.CustomerName) as [Customer Name],
        Customer.CustomerEbayname, 
        Customer.EmailAddress, 
        Customer.PhoneNumber, 
        (Customer.Address1 + SPACE(2) + Customer.Address2 + SPACE(2)+ Customer.City + SPACE(2) + Customer.PostCode + SPACE(2) + Customer.Country) as Address,  
        [Order].ItemPurchased, 
        [Order].PurchasedDate, 
        [Order].TotalPrice 
FROM    Customer 
        INNER JOIN [Order] 
            ON Customer.[CustomerID] = [Order].[CustomerID]  
WHERE   [PurchasedDate]= #" + date + "#

answered Mar 3, 2013 at 8:44

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

John Woo Over a year ago

use BETWEEN eg, WHERE [PurchasedDate] BETWEEN #date1# AND #date2#

John Woo Over a year ago

try this, WHERE [PurchasedDate] >= #date1# AND [PurchasedDate] <= #date2#

Amrit Sharma Over a year ago

Syntax error in date in query expression '[PurchasedDate] >= #date1# AND [PurchasedDate] <= #date2'.

John Woo Over a year ago

are you sure between is not supported? en.allexperts.com/q/Using-MS-Access-1440/… what version of access you are using?

Collectives™ on Stack Overflow

SQL query to retrieve data using date as where clause

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related