May 16, 2003 at 7:22 am
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'
May 16, 2003 at 7:30 am
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]
May 16, 2003 at 7:36 am
they both use Latin1_General
May 16, 2003 at 8:12 am
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]
May 16, 2003 at 8:46 am
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
May 16, 2003 at 8:55 am
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]
May 18, 2003 at 8:33 pm
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
May 19, 2003 at 12:02 am
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]
May 19, 2003 at 2:57 am
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.
May 19, 2003 at 4:03 am
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.
May 19, 2003 at 4:16 am
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://qa.sqlservercentral.com/scripts/contributions/325.asp for a script I posted to change the default laguange of all the users in one go.
May 20, 2003 at 8:59 am
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://qa.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
May 22, 2003 at 11:48 pm
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
May 23, 2003 at 1:37 am
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.
May 23, 2003 at 2:14 am
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]
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply