ITSagar ITSagar - 2 months ago 15
C# Question

MSACCESS DateTIme SQL QUERY

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.

Answer

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.

Comments