SQL 2000 date format problem after migration W2k to W2k3

Hello,

We are using SQL server 2000 on W2k Server and MS Access 2000 ADP
(like front-end). Now we try to change operating system for SQL Server
2000 from W2k to W2k3 and we found problem with date format - we
receive error: "Cannot convert date type varchar to datetime".

Datetime used in application are sent to SQL Server 2000 in format
YYYY-MM-DD as varchar. (in Query Analyzer all view/query works fine
but when application sends it as record source of any forms or stored
procedures it causes error). Moreover if we send date in format
YYYYMMDD it works OK, the same in case when we change FORMATDATE
before each SQL connection made by application (SET DATEFORMAT YMD)

What has happened after changing operating system?

thx for help

Re: SQL 2000 date format problem after migration W2k to W2k3

(Maciej07@gmail.com) writes:
> We are using SQL server 2000 on W2k Server and MS Access 2000 ADP
> (like front-end). Now we try to change operating system for SQL Server
> 2000 from W2k to W2k3 and we found problem with date format - we
> receive error: "Cannot convert date type varchar to datetime".
>
> Datetime used in application are sent to SQL Server 2000 in format
> YYYY-MM-DD as varchar. (in Query Analyzer all view/query works fine
> but when application sends it as record source of any forms or stored
> procedures it causes error). Moreover if we send date in format
> YYYYMMDD it works OK, the same in case when we change FORMATDATE
> before each SQL connection made by application (SET DATEFORMAT YMD)
>
> What has happened after changing operating system?

The format YYYY-MM-DD is not a safe format, but its interpretion is
up to dateformat and language settings. The default language (and thereby
the date format) is set on login level, and may be inherited from the
server. I would assume that on W2K you had installed SQL Server with
us_english as the default language, but on Win2003, you have an installation
with a language which uses a DMY format, under which YYYY-MM-DD works.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx