MSACCESS datetime SQL QUERY [英] MSACCESS datetime SQL QUERY
问题描述
您好。我来自印度（这可能与文化信息有关）。我正在使用MS-ACCESS 2010 32位作为后端在C＃.Net 2010 Express中构建桌面应用程序。我正在使用OLEDB进行数据库连接。
我有一个名为dt的列作为日期/时间，其具有以下值：
Hi. 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<br />
19-08-2016 22:54:24<br />
20-09-2016 22:56:01<br />
22-09-2016 22:56:27<br />
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
但这些都不适合我。请回复在sql查询中应该进行哪些更新，以按日期，按月和按年获取记录。提前致谢。
我的尝试：
我有还试过以下变化：
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.
What I have tried:
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
推荐答案
尝试使用如下查询截断日期
Try truncating the dates with a query like following
SELECT * FROM srvtrans WHERE DATEVALUE(dt)=DATEVALUE(@a) ORDER BY sno DESC
只需使用普通日期/时间作为参数值。有关更多信息，请查看 DateValue函数 - 访问 [ ^ ]
MS Access数据库引擎不知道DateTime.Parse（dt）！
将命令属性更改为：
MS Access database engine doesn't knowDateTime.Parse(dt)!
Change command properties to:
b.com.CommandText = "SELECT * FROM srvtrans WHERE dt=@a ORDER BY sno DESC";
b.com.Parameters.Add("@a", dtp_srdmy.Value);
，一切都应该没问题。
and everything should be OK.
这篇关于MSACCESS datetime SQL QUERY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
