date problem with SQL 2000

Question

date problem with SQL 2000

Viewing 11 posts - 16 through 25 (of 25 total)

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

David Burrows SSC Guru Points: 65142 More actions · Answer 1

Beware that the date/time format is dependant on several things. NT/2000, version of IIS, whether a user is logged in or not, whether users are impersonated, the system default locale. To name but a few, any of these could affect the date/time format.

Far away is close at hand in the images of elsewhere.
Anon.

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 2

Agree with a5xo3z1. Had a similar problem with older ASP app which was brought back from archives. Resolution was rather then to let the users 'type' a date, use a pop calendar in vbscript which interprete date according to client(local) settings but force date to 'dd MMM YYYY' when passing to SQL backend. Seems to fine for a intranet environment.

dk-fr Newbie Points: 2 More actions · Answer 3

We struggled some month with an application used in international environments. The problems are caused by date queries.

SQL 2000 interprets dates depending on the language of the server, the language of logins and the regional windows settings. The best way to get rid of this is to send date queries in the following format:

{ d 'yyyy-mm-dd'} for example: { d '2003-05-24' }

This proofs to work in all environments.

Dieter

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Hallo Dieter,

kaum zu glauben, ein weiterer deutscher SQL User. Hurra!!!

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 5

dk-fr, do your environment have , let say a german SQL server installation and a italian/french client for example?

If you say yyyy-mm-dd what is the 'SET DATEFORMAT' setting on the server?

Antares686 SSC Guru Points: 125444 More actions · Answer 6

quote:
dk-fr, do your environment have , let say a german SQL server installation and a italian/french client for example?
If you say yyyy-mm-dd what is the 'SET DATEFORMAT' setting on the server?

SET DATEFORMAT ymd

Hans Lindgren SSChampion Points: 10496 More actions · Answer 7

quote:
Its about the Date() and Now() function in ASP Programming. Which date format will these functions adopt?

There is a nice way of not struggeling when creating your AdHoc queries in ASP/VB. Use the nice function FORMAT. Ex:

Format(Now(), "YYYY-MM-DD HH:MM:SS")

As this format is a format that always works with SQL server.

Regards, Hans!

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 8

Hanslindgren, what if default dateformat on server is 'yyyy-dd-mm'? Most unlikely scenario!

Suppose an Italian setting client connecting to a German SQL Server will also run into trouble where the three character months are used.

Example Eng->Oct and German->Okt.

Agree your suggestion seems to be the better so far.

Hans Lindgren SSChampion Points: 10496 More actions · Answer 9

After some tests I happily note that I have learned something new and alot of old code can go in the trash or get some nice modifications 😉

Thanx to 5409045121009 for showing me the way out of Darkness and to Antarer686 for showing me into the Light 🙂

Regards, Hans!

dk-fr Newbie Points: 2 More actions · Answer 10

Thanks for your replies. Yes, I agree SET DATE FORMAT will work as well. But you need to know the date format your client application is sending to SQL.

We had these date problems with client applications sending dates in the current Windows date format without any formatting. If you use i.E. the Access Datepart function to create this ANSI date format you don't have to take care about it. These date formatting functions correspond to the regional settings of Windows clients.

And for me (sorry I'm German) the American date format looks as strange as { d 'yyyy-mm-dd'}.

Hallo Frank, sind hier wirklich so wenige Deutsche vertreten ? Danke für deinen Gruß. Man tut sich nicht immer leicht im Kreis der Gurus.

Dieter

renga78 SSC Rookie Points: 25 More actions · Answer 11

Change the query like this

SELECT * FROM table WHERE startdate > '14-Apr-2003'

or

SELECT * FROM table WHERE startdate > 'Apr 14 2003'

this will work

regards