problem with searching dates again

after more than one week asking on the forum .. i get allways an erro searching on dates in MS SQL 2000

myDate = smalldatetime

SELECT myDate FROM dbo.myTable WHERE (myDate >= '2003-12-15') AND (myDate < '2003-12-16')

i get the error :conversion from CHAR to SMALLMONEY out of the limits

why smallmoney ??

if i try

myDate = datetime

SELECT myDate FROM dbo.myTable WHERE (myDate >= '2003-12-15') AND (myDate < '2003-12-16')

i get the error :conversion from CHAR to DATETIME out of the limits

so much time and energy lost for those dates !

thank you for helping againWorks for me. Do you get any errors if you cut and paste and execute this code in Query Analyzer?set nocount on

create table #myTable1(myDate smalldatetime)
create table #myTable2(myDate datetime)

insert into #myTable1(myDate)
select '2003-12-14'
UNION
select '2003-12-15'
UNION
select '2003-12-16'
UNION
select '2003-12-17'

insert into #myTable2(myDate)
select myDate from #myTable1

SELECT myDate FROM dbo.#myTable1 WHERE (myDate >= '2003-12-15') AND (myDate < '2003-12-16')
SELECT myDate FROM dbo.#myTable2 WHERE (myDate >= '2003-12-15') AND (myDate < '2003-12-16')

drop table #myTable1
drop table #myTable2
Double check your datatypes.|||in my computer it works normally ...
but in my customer's computer : problem converting CHAR in SMALLMONEY

thanks a lot|||On your customer's QA interface, or in your customer's APPLICATION interface? There is nothing wrong with the code, so the errors is either in the data or (more likely) in the application interface.|||I'm a bit confused by your examples. I'm assuming that:myDate = smalldatetime

SELECT myDate FROM dbo.myTable WHERE (myDate >= '2003-12-15') AND (myDate < '2003-12-16')means that you have a table named myTable with a column named myDate that has a type of smalldatetime.

There is nothing that I can see that could be wrong with the code that you've posted. There has to be some significant difference between your example and what is causing your customer trouble. Please ask your customer to provide the SQL that they are executing (verbatim, via either cut-and-paste or sending you a .SQL file) and the output from executing: EXECUTE sp_help 'dbo.myTable'Examine what they send you carefully, I'm sure that the problem has to lie there somewhere.

-PatP|||BlindMan : in my customer's Query Analyser, i send to him the code to copy paste in it and he is getting this error
the datatype is only smalldatetime nothing else|||Pat
means that you have a table named myTable with a column named myDate that has a type of smalldatetime. : yes exactly

the database is in french but normally no matter .. for me it works without any problem

the code of blindman pasted in QA doesnt work for my customer in any database , even masters

the table is very simple .. this kind :

CREATE TABLE [dbo].[Users] (
[id_Users] [int] NOT NULL ,
[DateInscr] [smalldatetime] NULL ,
[Name] [nvarchar] (100) NULL
) ON [PRIMARY]
GO

i am totally lost and if you are 2 .. then i am finish :-)

i try EXECUTE sp_help 'dbo.myTable' ... i can save it as csv file is it right ?

thank you|||Please ask your customer to provide the SQL that they are executing

he is executing exactlly :

SELECT DateInscr FROM dbo.Users WHERE ((DateInscr >= '2003-12-15') AND (DateInscr < '2003-12-16'))

but he has this problem in any dates in any tables in any database in his SQL 2000 SERVER
we have tried over 10 differents columns with same result|||There is nothing wrong with the code. Your customer is making any one of a hundred different stupid mistakes that could probably be found and solved in 60 seconds if you were actually at his location.

You sent your customer MY code, and asked him to cut and paste it and execute it in his Query Analyzer EXACTLY as I wrote it, and it errors out?|||What country is he in? I wonder if it has to do with either his default collation or his international settings.|||You sent your customer MY code, and asked him to cut and paste it and execute it in his Query Analyzer EXACTLY as I wrote it, and it errors out?

right ! 100% right !
i copy it on skype and we are speaking .. not by email
he tell me what he is doing ... and he get an error at once
and i am turning crazy !!!

i know your code is right of course !!

the collation is french but mine too and it works

thanks a lot blindman .. thanks a lot to each one for your help|||Collations the same: Okay.

What about internation settings on the server? This could cause dates to be interpreted differently.

Try running sp_configure on both systems and compare the results to look for differences.|||If Blindman's code works for you, but not for your customer, then there is something different between your test system and your customer's production system. The code that Blindman sent you is not locale dependant in any way (that is the beauty of using ISO 8601 formatted dates), so the American versus French differences are just irrelevant.

I'm beginning to suspect that there is something seriuosly wrong with their SQL machine, either at the operating system or the SQL Server level. One way to test for that is to do a simple query against a table that has no corporeal form (it isn't on disk anywhere, it only exists within SQL Server's "mind" so to speak). Ask your customer to execute the SQL command:SELECT *
FROM master.dbo.sysprocesses
WHERE '1900-01-01' < last_batchIf this returns results, then the problem lies somewhere in what your customer is doing (code, table structures, or something else) but not in the server, else if it produces an error message or returns no results then the Operating System or SQL Server is corrupt.

-PatP|||he is trying this on 3 deiiferent databasesand get the same error , even on master
if i run

SELECT *
FROM master.dbo.sysprocesses
WHERE '1900-01-01' < last_batch

i get 22 rows ..it works for me

sp_configure works too for me :-) great !!

i must wait tomorow to try it when my customer is on Skype

thanks again|||anyway even for me it works with
2005-12-15
but not with
2005-12-02
if the day starts with a 0 i dont get any error but no rows are returned ...

for the moment the only way to get it for me

SELECT myDate
FROM myTable
WHERE
(CONVERT(DATETIME, myDate, 120)
BETWEEN
CONVERT(DATETIME, '2005/12/07 00:00:00', 120)
AND
CONVERT(DATETIME, '2005/12/08 00:00:00', 120))

i must solve this problem and it seems that MS SQL 2000 or 2005 doesnt work with dates normally|||MS SQL works with dates just fine.

Confirm: the code I sent you works without error on your machine?

Confirm: this code DOES generate an error on both your machine and the client's machine?:SELECT myDate
FROM myTable
WHERE
(CONVERT(DATETIME, myDate, 120)
BETWEEN
CONVERT(DATETIME, '2005/12/07 00:00:00', 120)
AND
CONVERT(DATETIME, '2005/12/08 00:00:00', 120))Sorry to be repetetive, but this is very odd and confusing, and there is a bit of a language barrier we are dealing with.|||yes this code with (CONVERT(DATETIME, myDate, 120) works fine for my customer and me

my customer has 4 computers and MSDE on his laptop and the same problem on each SQL server .. the code WHERE
(DateColumn >= '2005-06-01') AND (DateColumn < '2005-06-02') is not working at all on none of his computers

for me this code works but when a day starts with a 0, i get nothing ... and my table must return 100 rows|||This is a problem that we who do not use US format dates run into all the time. Converting your date string is really the only way of being sure that the code you write is going to run on any machine that you use it on. I think you will probably find that converting the smalldatetime field is unnecessary though.|||i have tried without converting it .. and sometimes it works sometimes not

WHERE
(DateColumn >= '2005-06-01') AND (DateColumn < '2005-06-02') doesnt works and it is not US neither UK nor FR format ...

as you say to be shure the only way is to convert the date ... MS SQL is not normally working with dates|||Quentin: I don't understand what your problem is, but I can assure you that when you use dates formatted using the ISO 8601 format (as 2005-06-15 23:59), SQL Server will ALWAYS interpret it correctly. There are no exceptions that I'm aware of, or have even heard mentioned.

If you believe that you've found an exception, PLEASE call Microsoft's Professional Support Services. If the problem is in your code, this might cost you a support call, but if it is a bug in the product Microsoft will fix the bug at no charge to you or anyone else. Either way, you'll have fixed the problem, and probably learned a LOT in the process.

If you don't want to open an incident yourself, please post sample scripts to build a table, insert data into it, and demonstrate the problem. I'll verify the problem, submit the incident myself, and make sure you get the solution.

-PatP|||Pat I thank you very much for your help and of course I trust you but
my customer have 4 SQL 2000 server installed on 4 computers + MSDE on a laptop and nowhere it works

for me it works without any problem except when a day stats with a 0 it returns no rows

what can I say ?
i am on it now since nearlly 3 weeks i must go on with real life

in theory it works, i believe it but at the end so many users have the same problem with dates

if I try
WHERE
(DateColumn >= '2005-06-01') AND (DateColumn < '2005-06-02')
on a smalldatetime colums my customer get an error in QA : SMALLMONEY conversion impossible

and i get the money from my customer :-)

thanks again Pat|||If you don't want to open an incident yourself, please post sample scripts to build a table, insert data into it, and demonstrate the problem. I'll verify the problem, submit the incident myself, and make sure you get the solution.

if my customer run the code of blindman in master database or any database

set nocount on

create table #myTable1(myDate smalldatetime)
create table #myTable2(myDate datetime)

insert into #myTable1(myDate)
select '2003-12-14'
UNION
select '2003-12-15'
UNION
select '2003-12-16'
UNION
select '2003-12-17'

insert into #myTable2(myDate)
select myDate from #myTable1

SELECT myDate FROM dbo.#myTable1 WHERE (myDate >= '2003-12-15') AND (myDate < '2003-12-16')
SELECT myDate FROM dbo.#myTable2 WHERE (myDate >= '2003-12-15') AND (myDate < '2003-12-16')

drop table #myTable1
drop table #myTable2

he gets

conversion from CHAR to SMALLMONEY out of the limits|||Yes Pat! Let Microsoft know that he gets an error when running the code of blindman.|||Blindman your code is perfectly working in my computer .. and i know your code is good, but it doesnt work at all in other SQL servers, i must sell an application and i cannot go myself everywhere to look what is wrong
then i must at the end choose the only way working in any case

and with

SELECT myDate FROM dbo.#myTable1 WHERE (myDate >= '2003-12-05') AND (myDate < '2003-12-06')

if the date starts with a 0 , in my computer, it returns no values|||Quentin, I understand that there is a bit of a language barrier here, but what you have been saying has not been consistent and does not make sense.

For example, you start out the above post with "Blindman your code is perfectly working in my computer", and you finish with "if the date starts with a 0 , in my computer, it returns no values". How the hell are any of us supposed to help you debug this? I'm afraid that, given the explanations we've gotten, most of the responders on this forum have been forced to attribute your problem to user error. We'd like to help, and we'd be genuinely professionaly interested if you've come across a true bug or installation issue, but with the information we've gotten so far diagnosing this is like trying to hit a moving target.|||In summary:
I strongly suspect that this is not a bug in SQL Server.
I strongly suspect that the problem will not be difficult to solve, once it is found.
I strongly suspect that we will not be able to diagnose this over the forum.

So, since this seems to represent part of your livelihood, I strongly encourage you to contract with a DBA in your area who can review the situation and probably fix it for you quickly and cheaply.

And I would strongly encourage you to come back to the forum and post the solution once it is found.