View Full Version : dates comparison with Access DB

03-10-2007, 01:20 PM
I have this:

"SELECT [....] FROM [adTerms] WHERE [adTerms.endTime]<#"& now &"#"

and it's not working.

the dates are in the same format.

I also tried datediff and it's not working - actually, it's interesting:
if i take out from the DB the endTime and do a dteDiff for it (with now()) the output is ok.
but if i do a select statement like this:

SELECT dateDiff('s',[endTime], now()) AS [theDifference]
then I get weird numbers...

What's wrong?!

03-10-2007, 01:35 PM
For date in a format of: dd/mm/yyyy you use # but for date/time function like now() you need to use single quotes:

"SELECT [....] FROM [adTerms] WHERE [adTerms.endTime]< '"& now &"'"

03-10-2007, 01:41 PM
Data type mismatch in criteria expression.

and the DB is in dd/mm/yyyy hh:mm:ss format.

03-10-2007, 01:48 PM
And before you got no error?
Try now()

03-11-2007, 12:24 PM
same error :\

I guess it's time to move on to mySql.

Thanks for trying to help.

03-11-2007, 03:17 PM
Try using this function:

Function SQLDate(ByVal sDate)
' returns sDate as SQLServer-friendly date string (ie: YYYYMMDDHHMMSS)
' if sDate is empty, returns string "NULL"
' eg usage: strMyDate = SQLDate(now())
dim rv : rv = "NULL"
dim sDateDay, sDateMonth, sDateYear, sDateHour, sDateMinute, sDateSecond
if isDate(sDate) then
sDateDay = day(sDate)
sDateMonth = month(sDate)
sDateYear = year(sDate)
sDateHour = hour(sDate)
sDateMinute = minute(sDate)
sDateSecond = second(sDate)
rv = "'" & sDateYear & "/" & addLeadingZero(sDateMonth) & "/" & addLeadingZero(sDateDay) & " " & addLeadingZero(sDateHour) & ":" & addLeadingZero(sDateMinute) & ":" & addLeadingZero(sDateSecond) & "'"
end if
SQLDate = rv
End Function

03-13-2007, 04:49 PM
it doesn't work.
it's time to move on to mysql.
I did some reading in the last days, and learned some basics of MySql.
But I'm gaving a little problem:

I used this code to add a record to table 'adItems' when I used Access.
once I added the record, I pulled the new id of it (just after it was created).

Set rs = Server.CreateObject("ADODB.Recordset") rs.open "select * from adItems",conn,3,3
rs("catId") = catId
rs("expiry") = expiry
rs("adTitle") = adTitle
newid = rs("id")

now, that I moved to MySql, newid remains blank. why is that? Any idea?

Thanks again

03-14-2007, 05:54 PM
You should start a new thread for new questions.

Here are some debugging techniques to try on your date problem.

Seeing as though you want to use NOW and NOW is also an Access function then you don't need to breakout to reference a variable.

SELECT * FROM table WHERE dateField < NOW()

If you want to test against any format abnormallities then you can use the FORMAT function

SELECT * FROM table WHERE FORMAT(dateField,'yyyy-m-d') < FORMAT(Now(),'yyyy-m-d')