[thelist] ASP and SQL and dates

Ken Schaefer ken at adOpenStatic.com
Sat Aug 16 00:46:15 CDT 2003


Couple of quick points:

a) You need to use the correct delimiters around your dates. You should be
using #, otherwise, if you supply 16/8/2003, that is 16 divided by 8 divided
by 2003, which is a very small number which equates to approximately 1st
January 1900 (which is the "base date" for Access).

Instead, you need to do something like:

WHERE
    FirstDate >= #1/8/2003#


b) Always use ISO-style formatting for dates when submitting them to Access.
This helps prevent various problems interpreting dates. So, dates should be
formatted: yyyy/mm/dd, eg 2003/08/16 for 16th August 2003

c) Some helpful URLs:
www.adopenstatic.com/faq/dateswithaccess.asp
www.adopenstatic.com/resources/code/formatdate.asp

HTH

Cheers
Ken

----- Original Message ----- 
From: "Lightening" <oktellme at earthlink.net>
To: "thelist" <thelist at lists.evolt.org>
Sent: Friday, August 15, 2003 11:27 PM
Subject: [thelist] ASP and SQL and dates


: This is similar to a question I asked earlier except that was an integer,
: this is a date. You guys recommended CInt (which fixed it) back then, so I
: tried CDate now, with no result. This code finds no records, although
there
: are records there.
:
: Can som one help me with this?
:
: thanks, Laura
: ________________________________________________
:
:
: <% ' this routine fills in holidays
: dim dsn
: dim sql
: dim rsHoliday
:
:  dsn="Provider=Microsoft.Jet.OLEDB.4.0;Data
: Source=c:\inetpub\wwwroot\WhiteLightening\db\buzz.mdb;Persist Security
: Info=False"
:
:   sql="SELECT HolidayId, HolidayName,  TimeOpen, TimeClose, HDate " &_
: " from HolidayTable where hdate >=" & cdate(firstDateOfMonth) & " and
hdate
: <= " & cdate(lastDateOfMonth)
:
: set rsHoliday=Server.CreateObject("ADODB.RecordSet")
: 'set cursor location
: rsHoliday.cursorlocation=adUseClient
:
: rsHoliday.Open sql,dsn
:
: if rsHoliday.eof then
:  response.write "<font align=center  color='#55eeee' >No Holidays were
found
: between " &_
:    firstDateOfMonth &  " and " & lastDateOfMonth &_
:      "</font><br>"
:
: else
:
: while not rsHoliday.EOF
:  response.write rsHoliday.Fields("HolidayName").value  & " " &
: rsHoliday.Fields("hdate").value  & "<br>"
:  rsHoliday.movenext
: wend
: end if
:
: rsHoliday.close
: set rsHoliday=nothing
:
: %>
:
:
: -- 
: * * Please support the community that supports you.  * *
: http://evolt.org/help_support_evolt/
:
: For unsubscribe and other options, including the Tip Harvester
: and archives of thelist go to: http://lists.evolt.org
: Workers of the Web, evolt !



More information about the thelist mailing list