Having trouble querying a specific date

[Bigced21]

I'm using sql server 2000.
What I'm trying to do is write a select statement to query all records with a date of 6-14-2004, and I'm not getting any results. Could someone please give me some ideas. In access it's easy as hell, but this is throwing me in a loop right now.

the data type for the date is datetime with a default value of (getdate())

 

[SQLBI]

Hi,

Have you tried using '2004-14-06 00:00:00' (Inc ' marks)?

I've had similar problems in the past ant this is the fix i've used.

Hope this helps.


Leigh Moore
Business Systems Manager
Vets4Pets Veterinary Group

http://www.vets4pets.eu.com

 

[john76]

The problem is most likely that when you type:

SELECT  *
  FROM  MyTable
  WHERE DateField = '06-14-2004'

SQL Server interprets the date as a datetime with the time portion set to midnight (00:00:00.000).

To get the full day in the query, you could try:

SELECT  *
  FROM  MyTable
  WHERE DateField BETWEEN '06-14-2004 00:00:00.000' AND '06-14-2004 23:59:59.999'

or

SELECT  *
  FROM  MyTable
  WHERE DateField >= '06-14-2004'
    AND DateField < '06-15-2004'

HTH,
John

PS - Someone else will probably have a 'better' way to code this shortly ;-)

 

[Bigced21]

Ok, John76 that worked!! Thanks!!
and thank you too leighmoore!!!!

 

[checkai]

SELECT  *
  FROM  MyTable
  WHERE convert(datetime,convert(char(10),DateField,101)) = '06-14-2004'

 

[john76]

checkai,

That will work, but I don't believe that all of that is neccessary:

SELECT  *
  FROM  MyTable
  WHERE convert(varchar, DateField, 101) = '06-14-2004'

works fine as well.

John