Using LIKE to search for datetime field 1

[Creeder]

Hi all,

I am basically doing a select base on a specific date

eg,

SELECT * FROM tbl WHERE sent_dt LIKE '%Feb 23 2001%'

Works fine but the problem occurs when i try to search for Dates that have only 1 digit for the day, for example

SELECT * FROM tbl WHERE sent_dt LIKE '%Feb 7 2001%'

and it does not return any records. I am 100% sure that there are records dated Feb 7 2001.

What could be wrong? I am using SQL 6.5. Any help is greatly appreciated.

YJ

 

[Andel]

Hi, is there any reason why you are using LIKE instead of '='? Andel
andelbarroga@hotmail.com

http://andyb.8m.com

 

[Creeder]

Hi Andel,

Thanks for replying. The problem with using the '=' is that it is an exact match. Let's say the table has 4 records with the sent date of Feb 27 2001 but the time section of the date differ from each other. When i do a

SELECT * FROM tbl WHERE sent_dt = 'Feb 27 2001' it does not return any records because i think sql is checking for
'Feb 27 2001 12:00AM' instead.

The only solution left is for to break up the datetime using DATEPART() function. I just want to know why the problem only occurs when searching for 1 digit day in the date.

Thanks in advance.

YJ

 

[Creeder]

Found the problem. It's because of the blanks.

When searching for 1 digit date you have include an additional blank in front of the day digit. eg

WHERE sent_dt LIKE '%Feb**7*2001%'

where the '*' represent a blank space. Hope this information is useful for any future newbies (like me).

YJ