Date Comparison with value in Database

Results 1 to 2 of 2

Thread: Date Comparison with value in Database

  1. #1
    Join Date
    Dec 1969

    Default Date Comparison with value in Database

    Hi,<BR> I&#039;m trying to create a page that checks<BR>a date in a field in a table in a simple access database against today&#039;s date.If today&#039;s date is &#060; than the date in the database I want it to print out the entire record.<BR><BR>Below is an example of my code:<BR><BR>Dim conn,rs,sSQL,today<BR>today = date &#039;today&#039;s date in the form dd/mm/yyyy<BR>set conn = server.CreateObject("ADODB.Connection")<BR>set rs = server.CreateObject("ADODB.Recordset")<BR><BR>conn .Provider = "Microsoft.Jet.OLEDB.4.0"<BR>conn.ConnectionSt ring = Application.Value("Connection2_ConnectionString")< BR>conn.Open<BR><BR>sSQL = "Select * FROM LiveTest WHERE LiveTest.Test_Date &#060;= &#039;"& today &"&#039;"<BR>set rs = conn.Execute (sSQL)<BR><BR><BR>if (rs.BOF) and (rs.EOF) then<BR>response.write rs("TestID")<BR>else<BR>Response.Redirect "Notest.htm"<BR>end if<BR> <BR>rs.Close<BR><BR>conn.Close<BR><BR>set conn = nothing<BR><BR>-----------------------------------------------<BR>The date is in the database in the form dd/mm/yyyy<BR>When I have the field to be of type date/time it gives me the mismatch error like below:<BR><BR>Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)<BR>[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.<BR>/FYPVersion1/performtestcheck.asp, line 13<BR>----------------------------------------------------------<BR><BR>And when I leave the field type as plain old text it doens&#039;t read the values.<BR>(I.E. just redirects to notest.htm)<BR><BR>Any pointers?<BR>All help greatly appreciated<BR>Ger

  2. #2
    Join Date
    Dec 1969

    Default Two answers...and two sub-answers

    (1) Read the ASPFAQs and/or the Access manual to see how literal dates must be entered in Access. What you are doing would work for any database *except* Access. (Oh, what the heck...Access needs #...# around dates instead of &#039;...&#039;)<BR><BR>(2) But WHY? Why go to this much work??? Access already has the builtin functions that will get today&#039;s date (or even the current time or or or or). So just have your query do<BR><BR>sSQL = "Select * FROM LiveTest WHERE LiveTest.Test_Date &#060;= Date()"<BR><BR>No need to get an external formatted date, at all! Just use the Date/Time value the Access itself finds for today.<BR><BR>COMMENTARY: *IF* the field LiveTest.Test_Date *actually* contains a date *and* time value, then this will *not* get matches on today! Reason: The Date() function returns today&#039;s date, alright, but with a time value of 0:00:00 AM!!! So if the field in the DB is today but at 9:30:55 AM, then it will *not* be less than or equal to Date()!! (Same thing would happen if you passed in an external date.)<BR><BR>Two solutions: (A) use "LiveTest.Test_Date &#060; ( Date() + 1 )" --- that is, less than *tomorrow* at midnight!<BR><BR>(B) Use "DateValue( LiveTest.Test_Date ) &#060;= Date()" --- the DateValue function strips off any time portion of a date/time field...same way it does in VBScript.<BR><BR><BR>

Posting Permissions

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