sql紧凑中的日期时间问题
我使用 Sql Compact3.5 作为我的数据库和 C# .NET 在不同的系统中，我得到的日期时间格式不同。在 Windows XP 中，它以 MM-dd-yyyy HH:mm:ss 格式检索日期时间，在媒体中心中，它以 MM/dd/yyyy hh:m:ss 格式检索日期时间。有什么方法可以使日期时间格式不受文化影响，或者我可以在sql紧凑中设置日期时间格式，以便让它成为任何只使用该格式的PC吗？
示例：
//TimeOfCall is passed as String using the format DateTime.Now.ToString("MM-dd-yyyy HH:mm:ss");
using (SqlCeConnection con = new SqlCeConnection(ConString))
{
using (SqlCeCommand SqlceCmd = new SqlCeCommand(
"Insert into myreports(TimeOfCall,Status) values(?,?)", con))
{
if (con.State == ConnectionState.Closed)
con.Open();
SqlceCmd.Parameters.Add(new SqlCeParameter("@TimeOfCall", strTimeOfCall));
SqlceCmd.Parameters.Add(new SqlCeParameter("@Status", strStatus));
int RowsaAffected = SqlceCmd.ExecuteNonQuery();
con.Close();
return RowsaAffected;
}
}
在重新获取记录时，查询的使用方式如下：
//FromTime and ToTime are passeed in the same format as while storing
using (SqlCeConnection con = new SqlCeConnection(ConString))
{
using (SqlCeDataAdapter SqlceDA = new SqlCeDataAdapter("Select TimeOfCall from myreports where TimeOfCall between '" + strFromTime + "' and '" + strToTime + "' order by TimeOfCall", con))
{
if (con.State == ConnectionState.Closed)
con.Open();
SqlceDA.Fill(dtReports);
con.Close();
return dtReports;
}
}
我希望它很清楚
I'm using Sql Compact3.5 as my DB with C# .NET In different systems i'm getting the datetime format differently. In an Windows XP it's retrieving the datetime in the format : MM-dd-yyyy HH:mm:ss and in Media center it's retrieving in the format : MM/dd/yyyy hh:m:ss. Is there any way to make the datetime format free from culture or can i set the datetime format in sql compact so let it be any PC it'll use that format only???
Example :
//TimeOfCall is passed as String using the format DateTime.Now.ToString("MM-dd-yyyy HH:mm:ss");
using (SqlCeConnection con = new SqlCeConnection(ConString))
{
using (SqlCeCommand SqlceCmd = new SqlCeCommand(
"Insert into myreports(TimeOfCall,Status) values(?,?)", con))
{
if (con.State == ConnectionState.Closed)
con.Open();
SqlceCmd.Parameters.Add(new SqlCeParameter("@TimeOfCall", strTimeOfCall));
SqlceCmd.Parameters.Add(new SqlCeParameter("@Status", strStatus));
int RowsaAffected = SqlceCmd.ExecuteNonQuery();
con.Close();
return RowsaAffected;
}
}
While Rertiving the record the query is used in this way :
//FromTime and ToTime are passeed in the same format as while storing
using (SqlCeConnection con = new SqlCeConnection(ConString))
{
using (SqlCeDataAdapter SqlceDA = new SqlCeDataAdapter("Select TimeOfCall from myreports where TimeOfCall between '" + strFromTime + "' and '" + strToTime + "' order by TimeOfCall", con))
{
if (con.State == ConnectionState.Closed)
con.Open();
SqlceDA.Fill(dtReports);
con.Close();
return dtReports;
}
}
I hope it's clear
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
好吧，从代码来看，你基本上是以错误的方式做正确的事情。
好消息是您正在使用参数 - 这是完全正确的 - 但是您实际上不需要不想在设置参数值之前将日期转换为字符串。
最简单的应该是将
SqlceCmd.Parameters.Add(new SqlCeParameter("@TimeOfCall", strTimeOfCall));更改为SqlceCmd.Parameters.AddWithValue("@TimeOfCall", timeOfCall));< /code> 其中 timeOfCall 是日期时间值。如果状态本身不是字符串，则这同样适用于状态。
如果您想更明确地了解类型，请先创建参数来定义类型，然后再设置它。
对于您的选择查询，执行相同的操作，将字符串连接替换为参数 @fromTime 和 @toTime，并直接从适当的 DateTime 值设置参数
Ok, from the code, you're basically doing the right thing the wrong way.
The good news is that you're using parameters - that's exactly right - however you don't need actually don't want to convert the date to a string before setting the parameter value.
Simplest should be to change
SqlceCmd.Parameters.Add(new SqlCeParameter("@TimeOfCall", strTimeOfCall));toSqlceCmd.Parameters.AddWithValue("@TimeOfCall", timeOfCall));where timeOfCall is a DateTime value.The same applies to the status if that's not natively a string.
If you want to be more explicit about types create the parameter first defining the type and then set it.
For your selection query do the same thing, replace your string concatenation with parameters @fromTime and @toTime and set the parameters directly from the appropriate DateTime values