...

View Full Version : dates comparison with Access DB



ShMiL
03-10-2007, 01:20 PM
Hi
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?!

BarrMan
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 &"'"

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

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

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

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

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

Thanks for trying to help.

BarrMan
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

ShMiL
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.AddNew
rs("catId") = catId
rs("expiry") = expiry
rs("adTitle") = adTitle
rs.Update
newid = rs("id")
rs.close



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

Thanks again

degsy
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')



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum