date problem with SQL 2000

Question

Post reply

date problem with SQL 2000

Gareth Ablett

SSC Enthusiast

Points: 195
More actions
May 16, 2003 at 7:22 am

#80903

with the bellow query it has worked for SQL 7 for ages a but now i'm testing it on SQL 2000 the query isnt working is this either the set or a new feature to SQL 2000
the problem being with the date.
SELECT * FROM table WHERE startdate > '14/04/2003'

Viewing 15 posts - 1 through 15 (of 25 total)

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

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

Hi,

could it be you're using a different collation order on SQL2k than on SQL7?

Cheers,

Frank

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

Gareth Ablett SSC Enthusiast Points: 195 More actions · Answer 2

Gareth Ablett

SSC Enthusiast

Points: 195

May 16, 2003 at 7:36 am

#457465

they both use Latin1_General

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

Hi,

I've tested it on my Server (SQL2k) and it runs.

Stupid question: Is startdate a datetime field?

Cheers,

Frank

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

Gareth Ablett SSC Enthusiast Points: 195 More actions · Answer 4

hi,

I really dont think I'm that stupid but I checked it and yep its definatly a datetime field.

I think i'm gonna have to play with this one for a while anyway.

Thanks for the help

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

I didn't mean that you're stupid, but my question.

I've made the experience that I sometimes look for the complicated and oversee the obvious.

Cheers,

Frank

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

mlwang Valued Member Points: 74 More actions · Answer 6

HI, I have face the same problem too last time. To get out of this trouble, i practise using yyy-mm-dd date format for all sql. It does save me lot of trouble to figure out what wrong with SQL2000.

select * from table where startdate = '2003-

04-14'

Hope it helps

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

Hi,

quote:
HI, I have face the same problem too last time. To get out of this trouble, i practise using yyy-mm-dd date format for all sql. It does save me lot of trouble to figure out what wrong with SQL2000.
select * from table where startdate = '2003-
04-14'

that's really ok for the moment, but in the long run you should figure out what is happening. I use the english version of SQL2k, all my queries use german date formatting dd.mm.yyyy and everything's fine. There must be some more or less severe reason for this.

Cheers,

Frank

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

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

Have you checked Regional Options on the server. I seem to remember having this problem before and always struggled with it. I have my servers set to British the date formatted to dd/mm/yyyy and dd mmm yyyy. I do what mlwang does and use yyyy-mm-dd format for input and matching. I know that if you use dd mmm yyyy format you will never get date problems but it can be a pain at times.

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

NPeeters SSChampion Points: 12536 More actions · Answer 9

Did you check the query plan? It might be possible that SQL Server decides to convert your datetime column to a string, before doing the comparison. Not only does this affect performance, but it might also yield the wrong result.

To be on the safe side, convert the literal date to a datetime value explicitely using CAST or CONVERT.

Antares686 SSC Guru Points: 125444 More actions · Answer 10

quote:
Have you checked Regional Options on the server. I seem to remember having this problem before and always struggled with it. I have my servers set to British the date formatted to dd/mm/yyyy and dd mmm yyyy. I do what mlwang does and use yyyy-mm-dd format for input and matching. I know that if you use dd mmm yyyy format you will never get date problems but it can be a pain at times.

I get the same problem and I am running my server under English. To get around I can use

SET DATEFORMAT dmy

however I don't need to for my purposes. So do check to make sure the default language is the same for both servers.

Also, you should note default language has to do with users created so open SQL Server Logins and look at the user you logged in with to make sure their settings are the same. If you find the server was setup wrong change then you can look here http://www.sqlservercentral.com/scripts/contributions/325.asp for a script I posted to change the default laguange of all the users in one go.

Gareth Ablett SSC Enthusiast Points: 195 More actions · Answer 11

quote:
quote:
Have you checked Regional Options on the server. I seem to remember having this problem before and always struggled with it. I have my servers set to British the date formatted to dd/mm/yyyy and dd mmm yyyy. I do what mlwang does and use yyyy-mm-dd format for input and matching. I know that if you use dd mmm yyyy format you will never get date problems but it can be a pain at times.
I get the same problem and I am running my server under English. To get around I can use
SET DATEFORMAT dmy
however I don't need to for my purposes. So do check to make sure the default language is the same for both servers.
Also, you should note default language has to do with users created so open SQL Server Logins and look at the user you logged in with to make sure their settings are the same. If you find the server was setup wrong change then you can look here http://www.sqlservercentral.com/scripts/contributions/325.asp for a script I posted to change the default laguange of all the users in one go.

Thanks that one fixed the problem I had set the default langauge to British English but the user accounts on there where still set to English, it was just lying under my nose :).

Thanks a million.

Parses over a big bag of cookies

mlwang Valued Member Points: 74 More actions · Answer 12

HI,

I would like to share a weird problem that I faced with my webserver. Its about the Date() and Now() function in ASP Programming. Which date format will these functions adopt? DomainServer or WebServer or SQLServer?

Thanks

AnzioBake SSCarpal Tunnel Points: 4134 More actions · Answer 13

The issue with dates comes up frequently in my experience. I use one of two ways to avoid the Problem: Either I send it as '20030629' (yyymmdd) or more often '2003 Jun 29'. Most systems seem to be able to interpret the alpha characters correctly, so the is no confusion with the day and month.

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

Hi mlwan,

quote:
HI,
I would like to share a weird problem that I faced with my webserver. Its about the Date() and Now() function in ASP Programming. Which date format will these functions adopt? DomainServer or WebServer or SQLServer?

I think, Now() and Date() return values according to the date and time settings on that computer on which they are called.

Cheers,

Frank

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