MSACCESS DateTIme SQL QUERY

Question

I am from India (it may has something to do with culture info here). I am building a desktop application in C#.Net 2010 Express with MS-ACCESS 2010 32 bit as backend. I am using OLEDB for db connectivity. I have a column named dt as Date/Time which has following values:

20-09-2016 22:53:32
19-08-2016 22:54:24
20-09-2016 22:56:01
22-09-2016 22:56:27
22-09-2016 22:56:41

I need to fetch the records By Date, By Month and By Year. Till now I am not able to complete the Date part so couldnt work on month and year. Following is my code:

b.com.CommandText = "SELECT * FROM srvtrans WHERE DateTime.Parse(dt)=@a ORDER BY sno DESC";
b.com.Parameters.Add("@a", dtp_srdmy.Value.ToShortDateString());
Show(dtp_srdmy.Value.ToShortDateString());
b.con.State == ConnectionState.Closed)
 con.Close();
mytemp = new DataTable();
da.Fill(mytemp);

I have also tried following variations:

WHERE CONVERT(VARCHAR(10),dt,111)=@a

WHERE CONVERT(VARCHAR(10),dt,101)=@a

WHERE dt LIKE '%@a%' 

WHERE DateTime.Parse(dt)=@a 

WHERE dt=DateValue(@a)

WHERE CAST(dt AS DATE)=@a

WHERE CONVERT(varchar, dt, 101)=@a

WHERE DATE(dt)=@a

WHERE dt=@a

but none of them works for me. Please reply what updation should be made in the sql query to fetch records by date, by month and by year. Thanks in advance.

In addition look at the line b.con.State == ConnectionState.Closed, you check for closed and then close it again after. What you should do is create a new OleDbConnection when needed and wrap this in a using block. See Best Practices - Executing Sql Statements which has some tips on how to best structure your ado.net code. — Igor
– Igor, Commented Sep 22, 2016 at 18:30

Igor · Accepted Answer · 2017-09-16 10:37:26Z

3

b.com.CommandText = "SELECT * FROM srvtrans WHERE dt = @a ORDER BY sno DESC";
b.com.Parameters.Add(new System.Data.OleDb.OleDbParameter("@a", OleDbType.DBDate) {Value = dtp_srdmy.Value });
con.Open();

You want to pass in the native types and not string representations of the types for your parameter values. I am assuming that dtp_srdmy is probably a user control (as pointed out by @Gord Thompson in the comments below probably a DateTimePicker) or other type where Value is a property that returns a DateTime instance.
You want to compare directly, the underlying store/provider will handle the comparison for you so do not try to wrap your values in ticks, quotes, or something else or try to do the conversion for the store engine.
You should specify the underlying access type using the OleDbType enumeration, I guessed it was DBDate but I could be wrong, please correct it if necessary.
Open your connection before you execute any commands. I assume you are using a data adapter but did not see it, make sure it is associated with your Command. I omitted this part of the code as it was not relevant to the question.

edited Sep 16, 2017 at 10:37

answered Sep 22, 2016 at 18:26

Igor

62.5k10 gold badges111 silver badges181 bronze badges

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

3 Comments

Gord Thompson Over a year ago

"I am assuming that dtp_srdmy is a nullable DateTime instance" - The "dtp_" prefix suggests that it is a DateTimePicker object whose .Value property is a DateTime. Still an excellent answer, though. +1

Igor Over a year ago

@GordThompson - ah hah, that makes more sense. Thanks!

ITSagar Over a year ago

Hi. I am sorry for mistakenly written con.close(). I have changes it to b.con.Open(); and yes, dtp is a c#.net datetimepicker control. Dataadapter is present and bound in a separate class. I tried the above suggestion with datareader and datadapter, but still getting 0 rows.

Shobhit Gupta · Accepted Answer · 2016-09-23 04:52:42Z

1

The following code should work definitely:

  b.com.CommandText = "SELECT * FROM srvtrans WHERE DATEVALUE(dt)=DATEVALUE(@a) ORDER BY sno DESC";
  b.com.Parameters.Add("@a", dtp_srdmy.Value);

Upvote if this helps.

answered Sep 23, 2016 at 4:52

Shobhit Gupta

6904 silver badges13 bronze badges

Collectives™ on Stack Overflow

MSACCESS DateTIme SQL QUERY

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related