Date Range Problem

[baden]

[MySQL] Having unsuccessful attempts at returing records that fall within a given date range...
Given two fields of either DATE or DATETIME types: {startEvent, endEvent} = {2/7/2005, 2/7/2005}

SQL:
SELECT * FROM table WHERE startDate <= '2/7/2005' AND endDate >= '2/7/2005' ORDER BY eventID

This returns nothing. I can just do a startdate > '2/7/2005' and that returns the record - but obviously not what we're trying to do here.

This does work w/ MS Access though:
SELECT * FROM table WHERE startDate <= #2/7/2005# AND endDate >= #2/7/2005# ORDER BY eventID


Any help please?!

 

[dalchri]

You might need to discard the time part of your search fields as follows:

SELECT * 
FROM table 
WHERE 
   DATE(startDate) <= '2/7/2005' And
   DATE(endDate) >= '2/7/2005' 
ORDER BY eventID

 

[baden]

Problem? MySQL date format differs. I forgot about a function I wrote a while ago - format ASP date to MySQL date... thus:

Function FormatSQLDate(dateTimeVal)
   'format ASP date values to MySQL dates
   FormatSQLDate = Year(dateTimeVal) & "-" & Month(dateTimeVal) & "-" & Day(dateTimeVal) & " " & Hour(dateTimeVal) & ":" & Minute(dateTimeVal) & ":" & Second(dateTimeVal)
   'Response.write FormatSQLDate & "<=="
End Function

Works great!