CODEWITHSUNDEEP
c#ms-access
Sand Man
Sand Man

Reputation: 85

Filter out by Date in SQL through c#

Hallo ladies and gentlemen

I have a problem with my sql in C#. So let me make this simple Here is my code:

OleDbConnection mydb;
OleDbCommand cmd;
OleDbCommandBuilder build;
OleDbDataAdapter adapter;
DataTable dt;
DataSet ds;
BindingSource bs;
String query;

public AnnualRateHistory()
{
    InitializeComponent();

    query = @"SELECT * FROM _Annual_Rate WHERE Date = 02/11/2014";
    build = new OleDbCommandBuilder(adapter);
    bs = new BindingSource();

    adapter = new OleDbDataAdapter(query, mydb);
    dt = new DataTable();
    adapter.Fill(dt);
}

Ok I removed a lot of code to make it simple but basically my problem must be at the sql. Code I removed was mydb.Open() and close and the connection as well. If I use that sql statement nothing shows up and it is set precisely to one of my data in the database. If I replace the '=' with '>' then everything shows up; if I use '<' then nothing shows up. I know that the date should be typed like this: '02/11/2014' but for some reason I get a data type mismatch and also on my other form it works without the '.

I can't find any errors with the SQL. Please tell me what am I doing wrong.

Here is the table schema definition:

enter image description here

And here is the data in the table:

enter image description here

Upvotes: 0

Views: 2601

Answers (3)

Erresen
Erresen

Reputation: 2043

Two problems -

  1. Your date is not really in the right format. It's usually best to use ISO8601 date format (i.e. yyyy-MM-ddTHH:mm:ss)

  2. You're asking the SQL server to return rows where the date is EXACTLY 02/11/2014, which means midnight on that day. If you want all records from that day then it should be:

    SELECT * FROM _Annual_Rate WHERE Date_Of_Rate > '2014-11-02' and Date_Of_Rate < '2014-11-03'

EDIT

Having seen your updates to the question, part 2 no longer really applies as it looks like you're only storing dates in the Date_Of_Rate date time field.

HOWEVER part 1 still applies. Dates should always be written in ISO8601 format when talking to SQL DB's. 

2/11/2014

means 2nd of November here in the UK, but in the US it means 11th of Feb. Many countries use different date formats and you can't always guarantee the way the server has been set up even in your own country. This has caused us (the company I work for) major headaches in the past. Before switching to ISO8601 dates, we specified dates to be written:

'dd-MMM-yyyy HH:mm:ss' (11-feb-2014 14:19:56)

which worked for a quite a while until a french company supplied a machine with an SQL server setup in french. Feb is Fév in french so all the software that comumicated with that machine bombed out. From then on we learnt our lesson. Always use ISO8601 date formats. They're the only way of getting totally un-ambiguous dates.

Upvotes: 2

Rhumborl
Rhumborl

Reputation: 16609

Looking at the schema, the problem seems to be that you are simply using the wrong column name - your column is called Date_Of_Rate but you are filtering on a column called Date.

Also Date is a keyword which is probably why you are getting the data mismatch error.

So just changing it to this should fix it:

query = @"SELECT * FROM _Annual_Rate WHERE Date_Of_Rate = '02/11/2014'";

NOTE: Syntax applies to MySQL / SQL Server as question was initially tagged incorrectly.

Upvotes: 1

Jdinklage Morgoone
Jdinklage Morgoone

Reputation: 810

I'm seeing two problems here with your SQL

  1. You're filtering on Date when the Column is actually Date_Of_Rate.
  2. You need to put "#" around your DateTime value in the query.

Your query should function if done like so:

query = @"SELECT * FROM _Annual_Rate WHERE Date_Of_Rate = #02/11/2014#";

Upvotes: 2

Related Questions