0
 Function FreeDate(tdate, tindex)
     Set RSD = Server.CreateObject("ADODB.recordset")
     RSD.Open "SELECT COUNT(*) AS Total FROM tTable WHERE fId = " & tindex & " and Convert(datetime, '"& tdate & "') BETWEEN StartDate AND EndDate", Conn
     FreeDate = RSD("Total")
     RSD.Close
     Set RSD = Nothing
End Function

This function returns 1 with the parameters '2018-12-05' and 31.

The following query on the database results in Total (0) :

SELECT Count(*) as Total
FROM tTable
WHERE Convert(datetime, '2018-12-05') BETWEEN StartDate AND EndDate AND fId = 31

I haven't been able to determine why the same query would not yield the same result.

James Z
  • 12,209
  • 10
  • 24
  • 44
Dislexic Programmer
  • 1
  • 1
    Does the query work with an ISO date format (e.g. `WHERE '20181205' BETWEEN StartDate AND EndDate AND fId = 31)`? The `CONVERT` is superfluous. – Dan Guzman Apr 15 '18 at 14:03
  • 1
    Also, use strongly typed [parameterized queries](https://stackoverflow.com/questions/10352211/vba-ado-connection-and-query-parameters) to avoid the need to format literals. – Dan Guzman Apr 15 '18 at 14:05
  • @DanGuzman You are correct on that one. I added `ISODate = Replace(tdate, "-", "")` to check this and the ISODate returns the expected values. – Dislexic Programmer Apr 15 '18 at 14:39

0 Answers0