Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    Jul 2002
    Posts
    436
    Thanks
    1
    Thanked 0 Times in 0 Posts

    dates comparison with Access DB

    Hi
    I have this:
    Code:
    "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:
    Code:
    SELECT dateDiff('s',[endTime], now()) AS [theDifference]
    then I get weird numbers...

    What's wrong?!

  • #2
    Senior Coder BarrMan's Avatar
    Join Date
    Feb 2005
    Location
    Israel.
    Posts
    1,644
    Thanks
    69
    Thanked 83 Times in 82 Posts
    For date in a format of: dd/mm/yyyy you use # but for date/time function like now() you need to use single quotes:
    Code:
    "SELECT [....] FROM [adTerms] WHERE [adTerms.endTime]< '"& now &"'"

  • #3
    Regular Coder
    Join Date
    Jul 2002
    Posts
    436
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Data type mismatch in criteria expression.

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

  • #4
    Senior Coder BarrMan's Avatar
    Join Date
    Feb 2005
    Location
    Israel.
    Posts
    1,644
    Thanks
    69
    Thanked 83 Times in 82 Posts
    And before you got no error?
    Try now()

  • #5
    Regular Coder
    Join Date
    Jul 2002
    Posts
    436
    Thanks
    1
    Thanked 0 Times in 0 Posts
    same error :\

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

    Thanks for trying to help.

  • #6
    Senior Coder BarrMan's Avatar
    Join Date
    Feb 2005
    Location
    Israel.
    Posts
    1,644
    Thanks
    69
    Thanked 83 Times in 82 Posts
    Try using this function:
    Code:
    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

  • #7
    Regular Coder
    Join Date
    Jul 2002
    Posts
    436
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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).

    Code:
    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

  • #8
    Senior Coder
    Join Date
    Nov 2002
    Location
    North-East, UK
    Posts
    1,265
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

    Code:
    SELECT * FROM table WHERE dateField < NOW()

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

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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •