469,621 Members | 1,666 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,621 developers. It's quick & easy.

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

Jun 25 '07 #1
1 3885
(Ma******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 25 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Cillies | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.