Weird Date problem

Question

Weird Date problem

Ninja's_RGR'us

SSC Guru

Points: 294069
More actions
May 27, 2004 at 8:41 am

#84584

I have an Access Data Project linked to our production server (SERVEUR4 : Win 2k server/ SQL srv 2k). We recently bought a new server (SERVEUR5 : win 2k3 same SQL version). But once I relink the adp to the new server I get weird date problems. EX:
Select * from Horaire where DateDebut = '2004/05/17' (ymd)
returns a conversion error from nVarChar to DateTime
but if I change the date to '2004/17/05' (ydm), the query works just fine, but result set returns the date in this format
'2004/05/17' (ymd) which is the one that I am using in the software. I've read that "SET DATEFORMAT ydm" can solve the problem but it works only for a single session. I need a more permanent solution that will work on the whole server for all users... and without altering the program.
I've also read that this can be solved in the regional parameters. I've copied all the parameters from server4 to 5 but it still doesn't work (format is yyyy/mm/dd and language is French Canada).

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

Check your Windows 2003 server locale settings as it might have been set using a different locale from your other server. Also check the server collation type in SQL (right click on server, go to properties, shows up under the general tab).

Shamless self promotion - read my blog http://sirsql.net

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

I checked for the user who set the local setting and it's been set by the local admin. Collation type is the same on SERVEUR5 as SERVEUR4 (FRENCH_CI_AS)

Language is French (France)

Could setting another collation which has other default settings correct the problem?

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 3

Remi,

Does the Query that is causing the problem on the new server run ok on the old?

Regional settings, Locale and Collation (try checking the indiv. database(s)) as well.

Good Luck

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Yes everything works fine on the old server. the problem occurs only when I try running the queries on the new server when they have dates after the 12th of the month.