[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