Search code examples
c#sql-serversql-server-2008.nettiers

executing queries with datetime in WHERE clause

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

  • 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.