Date Format in Select Query With C# To MS Access Database

Question

1.80/5 (2 votes)

See more:

Dear All,
I am writing C# with MS Access database.When I write select query with criteria as date.
Access database that isn't know date format when I select date over current year 2013.Example today is 28/09/2013,I enter date 14/09/2013 or 15/09/2013 or 25/09/2013 over 13.In the database,It know the date like that 13/09/2014 or 13/09/2015 or 13/09/2025.So how can I try to get exactly date as my parameter.Here is my writing code.

C#

string vFDate, vTDate, vStr;
vFDate = Convert.ToDateTime(txtFDate.Text).ToShortDateString();
vTDate = Convert.ToDateTime(txtTDate.Text).ToShortDateString();

//vFDate = 01/09/2013 or 12/09/2013 and vTDate = 28/09/2013 is ok,it's working.
//But
//vFDate = 18/09/2013 and vTDate = 28/09/2013 is not ok.So Date is changed.Like that
//vFDate = 13/09/2018 and vTDate = 13/09/2028

vStr = "Selec * From tbl Where R_Date Between  Format(#" + vFDate + "#, 'dd/mm/yyyy') And   Format(#" + vTDate + "#, 'dd/mm/yyyy')";
OleDbCommand cmd = new OleDbCommand(vStr, OleDBComm);
OleDBConn.Open();
cmd.ExecuteNonQuery();
OleDBConn.Close();

Posted 27-Sep-13 22:48pm

Member 10297003

Updated 27-Sep-13 23:59pm

Nelek

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2013-09-28T02:16:00

Don't do it like that! Why are you taking a string, converting it to a DateTime, then back to a string again, to add to another string, to pass to SQL where it has to be converted back to a DateTime to compare it? Use parametrized queries instead (You should be anyway to avoid SQL injection attacks)

C#

string vFDate, vTDate, vStr;
DateTime vFDate = Convert.ToDateTime(txtFDate.Text);
DateTime vTDate = Convert.ToDateTime(txtTDate.Text);
vStr = "SELECT * FROM tbl WHERE R_Date BETWEEN @FD AND @TD";
OleDbCommand cmd = new OleDbCommand(vStr, OleDBComm);
cmd.Parameters.AddWithValue ("@FD", vFDate); 
cmd.Parameters.AddWithValue ("@TD", vTDate);
OleDBConn.Open();
cmd.ExecuteNonQuery();

Chances are, you problem will disappear at the same time!

But if you must use a text box for date entry (and I'd use a DateTimePicker instead), then you should use DateTime.TryParse instead of Convert.ToDateTime.

Member 10297003 · Accepted Answer · 2013-10-01T05:00:00

Solution 2

Thanks you for your help. But I can Solve myself. By using like that following.

C#

string vFDate, vTDate;

vFDate = txtFDate.Value.ToString("yyyy/MM/dd");
vTDate = txtTDate.Value.ToString("yyyy/MM/dd");

And my select query like that

SQL

Select * From tbl where date between #" + vFDate + "# And #" + vTDate + "#"

Thanks everyone.

[Edit member="Tadit"]
Corrected formatting issues.
[/Edit]

Posted 1-Oct-13 5:00am

Member 10297003

Updated 1-Oct-13 5:11am

v2

Comments

Member 12068191 6-May-16 7:37am

HI BRO SAME PROBLEM HERE ... I'M RECORD DATA IN DB WITH DD/MM/YYYY FORMAT BUT RESULT NO ACCURATE....

Member 12068191 6-May-16 10:38am

THANKS BRO IT WORK FINE :) THNKS