dates again :o(

[Alistair]

diary_date = request.form("d iary_date") - (from a populated drop down
list)

strSQL = "SELECT saz_title, saz_text from saz_details where saz_date =#" &
diary_date & "#"

a response.write gives

SELECT saz_title, saz_text from saz_details where saz_date =#07/06/2004#

yet no records are found??

I have 4 records with dates 7/06/2004, stored in access 2000 DB as date/time

any ideas?...the sun is obviously making my head hurt as I should really
know all about dates by now.

many thanks

[Patrice]

Re: dates again :o(

Are you wroking using the MM/DD/YYYY format ?

Also make sure the date in the DB doesn't have hours minutes seconds...

Patrice

"Alistair" <forget_it> a écrit dans le message de
news:10cbq334bi 0ja5@corp.super news.com...[color=blue]
> diary_date = request.form("d iary_date") - (from a populated drop down
> list)
>
> strSQL = "SELECT saz_title, saz_text from saz_details where saz_date =#" &
> diary_date & "#"
>
> a response.write gives
>
> SELECT saz_title, saz_text from saz_details where saz_date =#07/06/2004#
>
> yet no records are found??
>
> I have 4 records with dates 7/06/2004, stored in access 2000 DB as[/color]
date/time[color=blue]
>
> any ideas?...the sun is obviously making my head hurt as I should really
> know all about dates by now.
>
> many thanks
>
>
>
>
>[/color]

[Mark Schupp]

Re: dates again :o(

reformat diary_date as yyyy-mm-dd

Also, date/time columns include time so it can be difficult to get a match
without using a range of dates. For example:

SELECT saz_title, saz_text from saz_details where saz_date >=#2004-07-06
00:00:00# and saz_date <#2004-07-07 00:00:00#

--
Mark Schupp
Head of Development
Integrity eLearning

https://www.ielearning.com

"Alistair" <forget_it> wrote in message
news:10cbq334bi 0ja5@corp.super news.com...[color=blue]
> diary_date = request.form("d iary_date") - (from a populated drop down
> list)
>
> strSQL = "SELECT saz_title, saz_text from saz_details where saz_date =#" &
> diary_date & "#"
>
> a response.write gives
>
> SELECT saz_title, saz_text from saz_details where saz_date =#07/06/2004#
>
> yet no records are found??
>
> I have 4 records with dates 7/06/2004, stored in access 2000 DB as[/color]
date/time[color=blue]
>
> any ideas?...the sun is obviously making my head hurt as I should really
> know all about dates by now.
>
> many thanks
>
>
>
>
>[/color]

[Alistair]

Re: dates again :o(


"Mark Schupp" <mschupp@ielear ning.com> wrote in message
news:ekGf6dXTEH A.2944@tk2msftn gp13.phx.gbl...[color=blue]
> reformat diary_date as yyyy-mm-dd
>
> Also, date/time columns include time so it can be difficult to get a match
> without using a range of dates. For example:
>
> SELECT saz_title, saz_text from saz_details where saz_date >=#2004-07-06
> 00:00:00# and saz_date <#2004-07-07 00:00:00#
>
> --[/color]

The drop down list is actually populated from the dates in the database..

so, if there are 4 records say 1/1/2004, 5/9/2004, 12/12/2004,3/9/2005 then
these are the options.

I have a script which pulls in those dates as they stand so why can't I then
select one of those dates??

<select name="diary_dat e">
<%
set rs = server.CreateOb ject ("ADODB.Records et")
strSQL = "SELECT saz_date FROM saz_details"
rs.Open strSQL, conn, 1
do while not rs.EOF
record = rs("saz_date")
response.write "<option>" & record & "</option>"
rs.movenext
loop
%>

^^^ populated list.

the results of which give me 4 dates in the format 07/06/2004

if I then take this and query the database

strSQL = "SELECT saz_title, saz_text from saz_details where saz_date =#" &
diary_date & "#"

....they dont exist!!!

[Bob Barrows [MVP]]

Re: dates again :o(

The dates are being displayed according to the Regional Settings for the
IUSR account. I strongly suspect the time component of your dates is being
dropped. Try Mark's suggestion:

where saz_date >=#" & diary_date & "# AND saz_date <#" & _
dateadd("d",1,C Date(diary_date )) & "#

Bob Barrows

Alistair wrote:[color=blue]
> "Mark Schupp" <mschupp@ielear ning.com> wrote in message
> news:ekGf6dXTEH A.2944@tk2msftn gp13.phx.gbl...[color=green]
>> reformat diary_date as yyyy-mm-dd
>>
>> Also, date/time columns include time so it can be difficult to get a
>> match without using a range of dates. For example:
>>
>> SELECT saz_title, saz_text from saz_details where saz_date[color=darkred]
>> >=#2004-07-06 00:00:00# and saz_date <#2004-07-07 00:00:00#[/color]
>>
>> --[/color]
>
> The drop down list is actually populated from the dates in the
> database..
>
> so, if there are 4 records say 1/1/2004, 5/9/2004,
> 12/12/2004,3/9/2005 then these are the options.
>
> I have a script which pulls in those dates as they stand so why can't
> I then select one of those dates??
>
> <select name="diary_dat e">
> <%
> set rs = server.CreateOb ject ("ADODB.Records et")
> strSQL = "SELECT saz_date FROM saz_details"
> rs.Open strSQL, conn, 1
> do while not rs.EOF
> record = rs("saz_date")
> response.write "<option>" & record & "</option>"
> rs.movenext
> loop
> %>
>
> ^^^ populated list.
>
> the results of which give me 4 dates in the format 07/06/2004
>
> if I then take this and query the database
>
> strSQL = "SELECT saz_title, saz_text from saz_details where saz_date
> =#" & diary_date & "#"
>
> ...they dont exist!!![/color]

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Alistair]

Re: dates again :o(


"Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
news:OkqbJ7XTEH A.2408@tk2msftn gp13.phx.gbl...[color=blue]
> The dates are being displayed according to the Regional Settings for the
> IUSR account. I strongly suspect the time component of your dates is being
> dropped. Try Mark's suggestion:
>
> where saz_date >=#" & diary_date & "# AND saz_date <#" & _
> dateadd("d",1,C Date(diary_date )) & "#
>[/color]

er..thanks...tr ied that...the response.write seemed correct, but a removal
of response.write, response.end... .

and my whole PC ground to a halt..the three fingered salute revealed
dllhost.exe had jumped to 95% of resources and it took over 2 minutes for a
dos widow to open so I sould restart IIS!!,and the page in question never
finished loading....

something is not right methinks

any ideas what causes things like that?

[Alistair]

Re: dates again :o(

sorry found it...

missing rs.movenext

thanks all

[Aaron [SQL Server MVP]]

Re: dates again :o(

Yeah, did you leave out a movenext perhaps? Reproduce the problem, and show
us the code that causes it.

--

http://www.aspfaq.com/

(Reverse address to reply.)




"Alistair" <forget_it> wrote in message
news:10cc07frff m721c@corp.supe rnews.com...[color=blue]
>
> "Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
> news:OkqbJ7XTEH A.2408@tk2msftn gp13.phx.gbl...[color=green]
> > The dates are being displayed according to the Regional Settings for the
> > IUSR account. I strongly suspect the time component of your dates is[/color][/color]
being[color=blue][color=green]
> > dropped. Try Mark's suggestion:
> >
> > where saz_date >=#" & diary_date & "# AND saz_date <#" & _
> > dateadd("d",1,C Date(diary_date )) & "#
> >[/color]
>
> er..thanks...tr ied that...the response.write seemed correct, but a removal
> of response.write, response.end... .
>
> and my whole PC ground to a halt..the three fingered salute revealed
> dllhost.exe had jumped to 95% of resources and it took over 2 minutes for[/color]
a[color=blue]
> dos widow to open so I sould restart IIS!!,and the page in question never
> finished loading....
>
> something is not right methinks
>
> any ideas what causes things like that?
>
>[/color]