Return only records that match a specifc date. 1

[sknyppy]

I'm having trouble returning records that match a specific date.

I have a datetime(8) field called myDate with the following data: 5/7/2003 8:00:00 AM

Now if I try to query all records that match the date it doesn't return any matches.
SELECT * FROM myTable
WHERE myDate = '5/7/2003'

I must be overlooking something really simple.

Thanks,
Dave

 

[hoggle]

since you have the time set to 8 AM you'll have to search through the entire day like this
SELECT * FROM myTable
WHERE myDate between '5/7/2003 12:00 AM' and '5/7/2003 12:00 PM'

 

[AtomicWedgie]

Maybe this could work?

SELECT * FROM myTable
WHERE left(myDate,8) = '5/7/2003'

 

[sadiqmcs]

you should write the query

SELECT * FROM myTable
WHERE myDate) = '2003/07/05'

 

[DataJanitor]

Well, those are some interesting suggestions. But, in implementing the between clause, if you do, WHERE myDate between '5/7/2003 12:00 AM' and '5/7/2003 12:00 PM', you'll only get the rows where the times are in the morning hours (12pm is noon). What you really want, is something more like &quot;between '5/7/2003 0:00' and '5/7/2003 23:59:59.999'. Or, you might use: &quot;where myDate >= '5/7/2003' and myDate < '5/8/2003', which will return everything without resorting to specifying times (default is midnight 0:00).

 

[Bygbobbo]

This should fix your problem.

SELECT * FROM myTable
WHERE convert(CHAR,myDate,101) = '5/7/2003'

/* convert(CHAR,myDate,101) == format mm/dd/yyyy */

Your initial problem is that the datetime stored is the date plus the time. In order to match on just date you have to remove the time.


Bygs

 

[sknyppy]

Thanks Bygbobbo for the help.
I found the answer over the weekend.

I specified: CONVERT(char(10),LogDateTime,101 )
You said: convert(CHAR,myDate,101)

Do I need to specify 10 chars or can I leave it off like you did?

Thanks,
sknyppy

 

[JamesLean]

Although converting the datetime to varchar will get rid of the time portion, if you use this method SQL Server will not be able to make good use of any index on that column due to the fact it has to calcualate the function for every row. For this reason using BETWEEN will perform much better:

WHERE mydate BETWEEN '20030705' AND '20030705 23:59:59'

Also note the use of an unambiguous date format - 'yyyymmdd hh:mm:ss'.

--James

 

[Bygbobbo]

For this particular :

convert(CHAR,<var>,101) you do not have to explicity give the declaration of CHAR(10).

In the query analyzer if you go to the help menu and select Transact-SQL help and type convert in the search box. Click on the CONVERT it will show you a bunch of samples related to datetime formatting..


hope this helps,
Bygs