November 11, 2002 at 1:31 pm
I have a table custlog with a field [contact-date] that is smalldatetime. If I isuue the following query:
SELECT [contact-date], FROM custlog WHERE [contact-date] LIKE '7/01/02%'
I get no records returned.
If I use '7/01/02' i get multiple records returned.
I thought this was the standard way of getting all times on a certain date. Any suggestions welcome!
Thank you
November 11, 2002 at 2:18 pm
Try:
SELECT [contact-date], FROM custlog
WHERE Convert(varchar,[contact-date],101) like '07/01/2002%'
OR
-- This will use the index, if there is one...
SELECT [contact-date], FROM custlog
where [contact-date] > Convert(smalldatetime,'07/01/2002')
and [contact-date] <= Convert(smalldatetime,'07/01/2002')
-Dan
Edited by - dj_meier on 11/11/2002 2:18:31 PM
-Dan
November 11, 2002 at 3:42 pm
You should not use wildcards with dates. Dates are not really char types they are numeric type when they are stored and are formated in the output. To get all times for a date as dj_meier states you can use his first item. However since the format will be the date portion only you will get better performance using an =, and there is no need for a wildcard.
SELECT [contact-date], FROM custlog
WHERE Convert(varchar,[contact-date],101) = '07/01/2002'
November 12, 2002 at 6:54 am
Thanks both Antares and dj_meier. Antares, I see your point and have incorporated it. Mr. Meier, I did try your suggestion but the results were no different than using just '701/02', I think Antares point explains it. Thanks for the help! There is always more to learn....
Jonathan
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply