Date formats

I am currently supporting an application that runs in SQL
Server 2000. One of our customers is having a problem
with filtering items from the database using dates. The
problem is that they can only filter on dates using US
date format, ie mm/dd/yy, yet once the day exceeds 12 they
can no longer view any data from the database. So if they
filter on 09/12/03 that will work fine and pull back data
from the 12th of September. If they filter on 09/13/03 it
does not pull anything back, even though there is data
from that day. Using UK format dates does not pull any
data back at all. This does not happen for any of my
other customers who are running the same application in
SQL Server 2000. Is there an option that can be set
either on the database or on the server that can affect
this?
Any ideas would be greatly appreciated.Ideally this requires a code change in your application.
Your app needs to pass dates around in a non ambiguous
format eg :- '13 Mar 2003', this format can cause problems
if you are running multiple languages though. The safeest
bet is yyyymmdd SQL will always interpret this as ymd.
If a code change isn't possible and you want a quick fix
for this client, compare the regional settings in control
panel to the troublesome machine to a known good one. I
imagine it's running English (US) and everyone else is
using English (UK) or vice versa.
HTH
Ryan
>--Original Message--
>I am currently supporting an application that runs in SQL
>Server 2000. One of our customers is having a problem
>with filtering items from the database using dates. The
>problem is that they can only filter on dates using US
>date format, ie mm/dd/yy, yet once the day exceeds 12
they
>can no longer view any data from the database. So if
they
>filter on 09/12/03 that will work fine and pull back data
>from the 12th of September. If they filter on 09/13/03
it
>does not pull anything back, even though there is data
>from that day. Using UK format dates does not pull any
>data back at all. This does not happen for any of my
>other customers who are running the same application in
>SQL Server 2000. Is there an option that can be set
>either on the database or on the server that can affect
>this?
>Any ideas would be greatly appreciated.
>.
>