Works 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)
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.
If it's not practically useful, then it's practically useless.
I'm a bit confused by your examples. I'm assuming that:
Originally Posted by quentin
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.
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:
WHERE '1900-01-01' < last_batch
If 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.