在 WHERE 子句中执行带有日期时间的查询

发布于 2024-09-26 04:36:31 字数 738 浏览 3 评论 0原文

我使用sql server 2008 R2作为数据存储。

到目前为止，在测试机器上，我拥有该软件的英文版本，并用于进行格式化日期时间字段的查询，因为

fromDate.ToString("MM/dd/yyyy");

现在我已将数据库部署在另一台意大利语服务器上。我将代码中的格式更改为

fromDate.ToString("dd/MM/yyyy");

是否有办法以中性格式进行查询？

谢谢！

编辑：

我忘了提及我正在将 NetTiers 与 CodeSmith 一起使用。这是一个完整的示例

AppointmentQuery aq = new AppointmentQuery(true, true);
aq.AppendGreaterThan(AppointmentColumn.AppointmentDate, fromDate.ToString("MM/dd/yyyy"));
aq.AppendLessThan(AppointmentColumn.AppointmentDate, toDate.ToString("MM/dd/yyyy"));
AppointmentService aSvc = new AppointmentService();
TList<Appointment> appointmentsList = aSvc.Find(aq);

原文

I use sql server 2008 R2 as a data store.

Until now on the test machine I had the english version of the software and used to make queries formatting the datetime field as

fromDate.ToString("MM/dd/yyyy");

now I have deployed the database on another server which is in the italian language. I shall change the format in my code to

fromDate.ToString("dd/MM/yyyy");

Is there a way to make the query in a neutral format?

thanks!

EDIT:

I forgot to mention that I am using NetTiers with CodeSmith. Here's a complete sample

AppointmentQuery aq = new AppointmentQuery(true, true);
aq.AppendGreaterThan(AppointmentColumn.AppointmentDate, fromDate.ToString("MM/dd/yyyy"));
aq.AppendLessThan(AppointmentColumn.AppointmentDate, toDate.ToString("MM/dd/yyyy"));
AppointmentService aSvc = new AppointmentService();
TList<Appointment> appointmentsList = aSvc.Find(aq);

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

嘿哥们儿 2024-10-03 04:36:31

<罢工>
您应该共享用于执行查询的代码，但我猜您正在使用字符串连接动态构建 SQL 查询来构建查询和参数。您应该使用参数化查询，然后可以将数据作为日期对象传递，而无需转换字符串。

例如，如果您的查询可能是这样的
DateTime fromDate = DateTime.Now; SqlCommand cmd = new SqlCommand( "select * from Orders where fromDT = @fromDate", con); cmd.Parameters.AddWithValue("@fromDate", fromDate); ...

作为一个很好的副作用，这将降低SQL 注入的风险。

更新：在您进行编辑后，问题上下文确实发生了显着变化，我不得不承认我对 .netTiers 项目的了解为零。但出于好奇，您是否尝试过直接传递日期实例，如下所示？

AppointmentQuery aq = new AppointmentQuery(true, true); 
aq.AppendGreaterThan(AppointmentColumn.AppointmentDate, fromDate); 
aq.AppendLessThan(AppointmentColumn.AppointmentDate, toDate); 
AppointmentService aSvc = new AppointmentService(); 
TList<Appointment> appointmentsList = aSvc.Find(aq);

You should share the code you are using to execute the query, but I guess you are building a SQL query dynamically using string concats to build the query and the arguments. You should rather use a parameterised query then you can pass the data as a date object and no need to converto a string.

For example if your query could be something like this
DateTime fromDate = DateTime.Now; SqlCommand cmd = new SqlCommand( "select * from Orders where fromDT = @fromDate", con); cmd.Parameters.AddWithValue("@fromDate", fromDate); ...

~~As a good side effect, this will reduce your risk of SQL injection.~~

Update: After your edit which does change the question context significantly, and I have to admit that I have Zero knowledge of the .netTiers project. But just out of curiosity have you tried just passing the date instances directly as in the following?

AppointmentQuery aq = new AppointmentQuery(true, true); 
aq.AppendGreaterThan(AppointmentColumn.AppointmentDate, fromDate); 
aq.AppendLessThan(AppointmentColumn.AppointmentDate, toDate); 
AppointmentService aSvc = new AppointmentService(); 
TList<Appointment> appointmentsList = aSvc.Find(aq);

回复收藏 0 原文