Annoying time birt in a Date Field

[Brad]

Hi,

I have an SQL Table which holds a time stamp field, in the format here:

13-01-2003 11:06:45

I need to only search on the Date bit, not the time bit, but when I try the following sql string in my ASP page, I get the following:

SQLStr = "SELECT INFOEDIT.EDITDATE, INFOEDIT.REFNO, INFOEDIT.NEWUSERMN, INFOEDIT.STATUS, INFOEDIT.TEXT, INFOEDIT.USERMN FROM INFOEDIT WHERE INFOEDIT.USERMN = '" & logmne & "' AND INFOEDIT.TEXT Like '" & criteria & "%' AND INFOEDIT.EDITDATE Like " & cdate(logdate) & " ORDER BY INFOEDIT.REFNO DESC;"

The reason I employed the CDate function, was to try and get rid of the time bits, but it didn't help.

Can anyone advise how to ignore the time part of the time stamp field, please ?

I know the code works, apart from the date criteria, as I've removed it and run it fine.

Thanks in advance,
Brad.

PS - One day, When I know whatr I'm doing, I'll return the favour.

[arnyinc]

edit:

It depends what database you're using (SQL Server 2000, Access, Oracle, mySQL). It may not make sense to use "LIKE" with a date field.

Basically you need to use database-specific commands to make INFOEDIT.EDITDATE be in the format of your search string.

[Morgoth]

Maybe this will help:
<%
Response.Write Year(Now()) & "<br>"
Response.Write Month(Now()) & "<br>"
Response.Write Day(Now()) & "<br>"
%>

[whammy]

Also, I've never tried using "LIKE" without '%whatever%' delimiter(s). I can't test it at the moment, but it can't hurt to try anyway...

[Morgoth]

"LIKE"
Is that real SQL syntax?

lol
How does it work?

[aCcodeMonkey]

Brad

Use the CONVERT() function to convert/CAST the two DateTime variables into the same format.

Syntax:
CONVERT(DataType, Field, Int)

Examples:

SELECT Cases
FROM myTable
WHERE CONVERT(char(24),dbo.table_case.creation_time,109) = CONVERT(char(24),"& CDate(myDateTime) & "',109)

If you are doing Time Calcs:
DateDiff(dd,dbo.table_case.creation_time,CONVERT(char(24),"& CDate(myDateTime) & "',109))

Hope this helps :cool: