executing queries with datetime in WHERE clause

Question

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);

As others have said, but it's worth repeating, you really shouldn't be doing this. — ProfK, Oct 05 '10 at 18:09
to be fair to @ProfK, he commented before your edit which does change the context of the question considerably. — Chris Taylor, Oct 05 '10 at 18:53

Chris Taylor · Answer 1 · 2010-10-05T18:59:24.410

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);

no I am not using string concats. Please see my edit for more details. I have forgotten to mention that I was using the Strong typed query objects from NetTiers. thanks — Lorenzo, Oct 05 '10 at 18:18
@Lorenzo, I updated my response to address the new info you provided in your question, but this has taken the question into a technology I have not worked with take what I say here with a grain of salt. — Chris Taylor, Oct 05 '10 at 19:01
No Chris I cannot pass to the various append methods date instances because they only accept string parameters. :) — Lorenzo, Oct 08 '10 at 13:29

score 2 · Accepted Answer · answered Oct 06 '10 at 06:40

ISO 8601 Data elements and interchange formats — Information interchange — Representation of dates and times allows both the YYYY-MM-DD and YYYYMMDD. SQL Server recognises the ISO specifications.

Although the standard allows both the YYYY-MM-DD and YYYYMMDD formats for complete calendar date representations, if the day [DD] is omitted then only the YYYY-MM format is allowed. By disallowing dates of the form YYYYMM, the standard avoids confusion with the truncated representation YYMMDD (still often used).

I prefer the YYYYMMDD format, but I think that's because I only knew about that to start with, and to me it seems more universal, having done away with characters that might be considered locale specific.

score 1 · Answer 3 · answered Oct 05 '10 at 17:47

1

Personally, I always use yyyy-MM-dd. This also makes it sortable as a string.

However, a date is a date is a date. There's no need to change the date to a string. In .NET, user DateTime.

answered Oct 05 '10 at 17:47

Brad

15,361
6
36
57

executing queries with datetime in WHERE clause

3 Answers3