how to search smalldatetime field with a querystring value? ASP/SQL

Report · Jul 31, 2008

I'm passing the following date, via a querystring, to a page that has a
recordset containing a smalldatetime field:
page.asp?eventdatetime=2008-09-26

Problem is, the recordset is either producing no results, despite there
being events in the table, on that date. If I change the querystring to any
of the following:
dd-mm-yyyy
dd/mm/yyyy
yyyy/mm/dd

I get an error about data type mismatch.

I know this is something silly...can anyone tell me what I'm doing wrong?
Thank you.

Regards
Nath.

Report · Jul 31, 2008

tradmusic.com wrote:
> I'm passing the following date, via a querystring, to a page that has a
> recordset containing a smalldatetime field:
> page.asp?eventdatetime=2008-09-26
>
> Problem is, the recordset is either producing no results, despite there
> being events in the table, on that date. If I change the querystring to any
> of the following:
> dd-mm-yyyy
> dd/mm/yyyy
> yyyy/mm/dd
>
> I get an error about data type mismatch.
>
> I know this is something silly...can anyone tell me what I'm doing wrong?
> Thank you.
>
> Regards
> Nath.
A querystring is a string value, you need to convert it to a date value
before the recordset will accept it. Maybe CDATE will do it:
http://www.w3schools.com/VBscript/func_cdate.asp

Steve

Report · Jul 31, 2008

I changed my select statement to the following:

SELECT rowID, title, subtitle, summary, eventdatetime, price FROM dbo.TABLE
WHERE CONVERT(char(10), eventdatetime, 120) = ? ORDER BY eventdatetime ASC

This works, and I get results in my recordset.

...problem is, I want to display the date, just as a title, on the page but
whenever I add any of these:
<%= DoDateTime((rsDailySchedule.Fields.Item("eventdatetime").Value), 1,
1033) %>
<%= DoDateTime((rsDailySchedule.Fields.Item("eventdatetime").Value), 2, -1)
%>

...I get this error:
Type mismatch: 'DoDateTime'

...yet, when I put this on the page (without any date formatting):
<%=(rsDailySchedule.Fields.Item("eventdatetime").Value)%>

...it displays the smalldatetime as DD/MM/YYYY HH:MM:SS?

I need to display the date as "Monday 17th July 2008", without the time.

I really don't understand what I'm doing wrong here. Hope someone can help.

Regards
Nath.

"Dooza" <doozadooza@gmail.com> wrote in message
news:g6shmr$sq2$1@forums.macromedia.com...
> tradmusic.com wrote:
>> I'm passing the following date, via a querystring, to a page that has a
>> recordset containing a smalldatetime field:
>> page.asp?eventdatetime=2008-09-26
>>
>> Problem is, the recordset is either producing no results, despite there
>> being events in the table, on that date. If I change the querystring to
>> any of the following:
>> dd-mm-yyyy
>> dd/mm/yyyy
>> yyyy/mm/dd
>>
>> I get an error about data type mismatch.
>>
>> I know this is something silly...can anyone tell me what I'm doing wrong?
>> Thank you.
>>
>> Regards
>> Nath.
> A querystring is a string value, you need to convert it to a date value
> before the recordset will accept it. Maybe CDATE will do it:
> http://www.w3schools.com/VBscript/func_cdate.asp
>
> Steve

Report · Jul 31, 2008

Hi,

Also, despite spending hours searching, I can't seem to find a way to
display the time, from a smalldatetime field, as:

HH:MM AM/PM

I've tried:
vbShortTime
vbLongTime

...there doesn't appear to be a happy medium, yet it appears to me to be the
most obvious format! Gotta love "code". :o)

Any ideas how I display the time in my (everybody's!) preferred format? I
can't find a function or anything out there, just a lot of scratching of
heads.

Really appreciate any help offered. Thanks.
Nath.

"Dooza" <doozadooza@gmail.com> wrote in message
news:g6shmr$sq2$1@forums.macromedia.com...
> tradmusic.com wrote:
>> I'm passing the following date, via a querystring, to a page that has a
>> recordset containing a smalldatetime field:
>> page.asp?eventdatetime=2008-09-26
>>
>> Problem is, the recordset is either producing no results, despite there
>> being events in the table, on that date. If I change the querystring to
>> any of the following:
>> dd-mm-yyyy
>> dd/mm/yyyy
>> yyyy/mm/dd
>>
>> I get an error about data type mismatch.
>>
>> I know this is something silly...can anyone tell me what I'm doing wrong?
>> Thank you.
>>
>> Regards
>> Nath.
> A querystring is a string value, you need to convert it to a date value
> before the recordset will accept it. Maybe CDATE will do it:
> http://www.w3schools.com/VBscript/func_cdate.asp
>
> Steve

Report · Aug 01, 2008

tradmusic.com wrote:
> I changed my select statement to the following:
>
> SELECT rowID, title, subtitle, summary, eventdatetime, price FROM dbo.TABLE
> WHERE CONVERT(char(10), eventdatetime, 120) = ? ORDER BY eventdatetime ASC
>
> This works, and I get results in my recordset.
>
> ...problem is, I want to display the date, just as a title, on the page but
> whenever I add any of these:
> <%= DoDateTime((rsDailySchedule.Fields.Item("eventdatetime").Value), 1,
> 1033) %>
> <%= DoDateTime((rsDailySchedule.Fields.Item("eventdatetime").Value), 2, -1)
> %>
>
> ...I get this error:
> Type mismatch: 'DoDateTime'
>
> ...yet, when I put this on the page (without any date formatting):
> <%=(rsDailySchedule.Fields.Item("eventdatetime").Value)%>
>
> ...it displays the smalldatetime as DD/MM/YYYY HH:MM:SS?
>
> I need to display the date as "Monday 17th July 2008", without the time.
>
> I really don't understand what I'm doing wrong here. Hope someone can help.

I would do it in your SQL. If your using MS SQL you can use something
like this:

SELECT DATENAME(DW,GETDATE()) + ' ' + DATENAME(MM, GETDATE()) +
RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY]

This creates: Friday August 01, 2008 which is pretty close to what you
want.

But, I would like to see the code for the ASP function DoDateTime as
that is where your type mismatch is happening.

Steve