SQL 2000 date format problem after migration W2k to W2k3

Maciej07

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

Subscribe Reply

4037

Erland Sommarskog

(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****@sommarsk og.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

Similar topics

7823

Help! VB.Net Date to Access 2000 Short Time

by: Jay | last post by:

I previously posted this question under Visual Basic newsgroup, but was advised to re-post here. I'm hoping someone can help me solve an issue I'm having with VB.Net and Access 2000. Here's the issue. I hope I've included all relevant information. On a form, I have a DateTimePicker with the following

.NET Framework

34907

what is equivalent of format(date) function of MS Access in MS Sql Server 2000

by: Leader | last post by:

Hi All, I am facing a problem with a sql what i used in MS Access but its not returning the same result in MS Sql Server 2000. Here i am giving the sql: SELECT TOP 3 format( MY_DATE, "dddd mm, yyyy" ) FROM MY_TAB WHERE MY_ID=1 The above sql in ACCESS return me the date in below format in one column:

Microsoft SQL Server

3161

Date inputs (UK format) in SQL svr 2000 with OLE DB

by: Niall Porter | last post by:

Hi all, I'm building an ASP app on a Windows 2000/IIS machine which interfaces with our SQL Server 2000 database via OLE DB. Since we're based in the UK I want the users to be able to type in dates in UK date format to input into the database. In Enterprise Manager on the SQL Server I can manually enter a record into a table and just type in a UK date (MM/DD/YYYY e.g. 25/12/2004) and it accepts it happily.

Microsoft SQL Server

1934

Migrating large amounts of data from SQL Server 7.0 to 2000

by: Serdar Yegulalp | last post by:

I'm in the process of migrating a lot of data (millions of rows, 4GB+ of data) from an older SQL Server 7.0 database to a new SQL Server 2000 machine. Time is not of the essence; my main concern during the migration is that when I copy in the new data, the new database isn't paralyzed by the amount of bulk copying being one. For this reason, I'm splitting the data into one-month chunks (the data's all timestamped and goes back about 3...

Microsoft SQL Server

1897

Can anyhelp me, what does this javascript calendar give error for dates before year 2000?

by: Fendi Baba | last post by:

The function is called from opencalendar(targetfield). Thanks for any hints on what could be the problem. .............................................................. var decimalPointDelimiter = ".";

Javascript

1913

Controlling Dates in Access 2000

by: Cillies | last post by:

Does anyknow how to control dates in acess 2000, the thing is I want to search for records using a form by date. I will be using a between date search box. But i want to enter say todays date but retrieve the results for exactly one month later. E.G. I if I enter 10-05-04 to 14-05-04 I want to see the records for exactly 30/31 days later. I was thinking could I manipulate the "Today'sDate" function + 30days.

Microsoft Access / VBA

2930

Incorrect Date posting Access 2000 SQL statement

by: m_t_hill | last post by:

Running MS Access 2000 MS Windows XP Pro This has caused me a lot of hair loss in the last few days so would appreciate any help. I am running code to append/update a local access database from a remote MS SQL database. The method of determining which records to append is primarily using the last date an update was carried out; I appreciate that there is probably a better way of doing this but time

Microsoft Access / VBA

3160

MS Access 2000 VBA Code Shows Totally Incorrect Date from Controls on a Form

by: Easystart | last post by:

Hi, Sorry for my English. English is not my native tougue. I am working in MS Access 2000 with a SQLServer 2000 Backend database. MS Access 2000 is my GUI front end that has SQLServer linked tables in it. One of my forms has two TEXT BOX controls formated as Short Date. The form is binded to a linked table. The linked tables has about 7 records and one of the control is binded to a table field. These two controls displays the date...

Microsoft Access / VBA

2738

Access 2000 to 2003 conversion missing reference and freezing errors

by: NEWSGROUPS | last post by:

I am in the midst of trying to convert about 25 Access 2000 to Access 2003. The new environment consists of Office/Access 2003 and Outlook 2003. When converting the back ends I have no problems. When converting the front ends I get the following issues and error. If I try to import all the objects into a blank Access 2003 shell I get a missing reference error for Outlook 2000, Excel 2000, and Word 2000 or after importing all the objects...

Microsoft Access / VBA

8361

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

8278

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

8807

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

8701

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

6158

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

4144

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

4290

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

2701

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

1912

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP