从表中selectsql ce服务器返回0之间的date

我正在开发一个Windows窗体应用程序在C#中。 我有一个查询,我想从dateselectdate。 结果总是0.我的DB是一个.sdf文件,在我的表中我有一个date时间字段来存储订单dateDateTime.Now,我的PCdate格式是10.12.2014,任何人可以请帮我解释这是为什么hapening ..! 有没有更好的文件格式来保存本地? 该应用程序将被安装在几台PC上,将date时间格式成为一个问题。 你的帮助非常appricaitaed ..检查请提供一些代码。

internal static DataTable SearchItemsInOrders(string searchWord, string table, int minValue, int maxValue, out DataTable dtTable, DateTime dateFrom, DateTime dateTo) { string commandText = "select A.ItemName, B.Site, B.OrderId, A.Qty, B.Requester, B.Receiver, B.Date "; commandText += "From tblOrderLine AS A Inner join tblOrder As B on A.OrderId=B.OrderId "; commandText += "where A.ItemName='" + searchWord + "' and B.Date >= '" + dateFrom.ToShortDateString() + "' and B.Date <='" + dateTo.ToShortDateString() + "' Order By A.OrderId DESC"; SqlCeDataAdapter adp = new SqlCeDataAdapter(); SqlCeConnection con = ConAndData.Con; dtTable = new DataTable(); adp = new SqlCeDataAdapter(commandText, con); if (con.State == System.Data.ConnectionState.Closed) { con.Open(); } adp.Fill(minValue, maxValue, dtTable); return dtTable; } 

Solutions Collecting From Web of "从表中selectsql ce服务器返回0之间的date"

尝试使用这个查询,

 string commandText = "select A.ItemName, B.Site, B.OrderId, A.Qty, B.Requester, B.Receiver, B.Date "; commandText += "From tblOrderLine AS A Inner join tblOrder As B on A.OrderId=B.OrderId "; commandText += "where A.ItemName='" + searchWord + "' and CONVERT(NVARCHAR(10),B.Date,121) >= CONVERT(NVARCHAR(10),'" + dateFrom.ToString("yyyy-MM-dd") + "',121) and CONVERT(NVARCHAR(10),B.Date,121) <= CONVERT(NVARCHAR(10),'" + dateTo.ToString("yyyy-MM-dd") + "',121) Order By A.OrderId DESC"; 

希望这可以帮助…

正如在评论中所建议的 – 你必须使用参数化查询。

所以,你的commandText应该是这样的:

 string commandText = "select A.ItemName, B.Site, B.OrderId, A.Qty, B.Requester, B.Receiver, B.Date "; commandText += "From tblOrderLine AS A Inner join tblOrder As B on A.OrderId=B.OrderId "; commandText += "where A.ItemName=@ItemName and B.Date >= @DateFrom and B.Date <=@DateTo Order By A.OrderId DESC"; 

稍后您应该将参数和值添加到命令中:

 adp = new SqlCeDataAdapter(commandText, con); adp.SelectCommand.Parameters.Add("@ItemName", SqlDbType.NVarChar).Value = searchWord; adp.SelectCommand.Parameters.Add("@DateFrom", SqlDbType.DateTime).Value = dateFrom; adp.SelectCommand.Parameters.Add("@DateTo", SqlDbType.DateTime).Value = dateTo; 

这也将防止由ToShortDateString()产生的不正确的日期格式导致的可能的问题。