Question about Database Results on ASP -- look for today's date?

Factual Questions
1

Argh. I’ve spent four hours trying to figure this out. And I just can’t.

OK – here’s my situation. I run a basic website for an organization with limited personnel – and those personnel have next-to-no web training.

Our website has a sidebar that contains a “Week at a Glance” calendar. Up til now, it’s been manually updated.

Today, I’ve been playing around with database results for that box. I created an Acces database with a record for each day – containing an “eventdate” field (couldn’t just use “date” since it’s reserved) and and “events” field and saved that database to our server.

I told the database results page to display the first seven records. Lo and behold! It worked.

BUT …
that was before I tried to figure out how to tell it to make the first date it shows TODAY’S date, and show the next six from there. That way, my limited-training update person can update the database, and that sidebar automatically pulls events for today’s date and the next six as well and displays them.

I won’t go into what all I’ve tried, but suffice it to say that I can’t find anything online that tells me how to do what I want to do.

Everything I know about SQL I’ve learned today, so I am a MAJOR amateur with it. But here’s the code I think I need, with the exception of where the question marks are:

SELECT eventdate, events
FROM ataglance [that’s the name of my database table]
WHERE (eventdate = ???)

In that question mark spot, I’ve tried, well, everything:
Date()
SYSDATE

And a couple of others.

If I get this figured out, then I figure it’s an easy step to tell it to pull events from today’s date PLUS one (For tomorrow’s events). Right?

Be gentle with me. I’m scared. :smiley:

2

Well, for MySQL, you can do something like


SELECT ... WHERE eventdate >= now() LIMIT 7

to start at today and grab 7 total events (today and the next 6). Unless I’m blind, you didn’t specify what database software you’re using, so you might have to customize that a little bit. (Or maybe not; I’m only familiar with MySQL, since it’s free. :wink: )

3

From my original post:

So it’s there, though misspelled, so you’re not blind, Hauky. :smiley:

Thanks for the input. I didn’t try your solution … because I figured it out eventually. Not sure what I changed, but suddenly the code worked. The working SQL code ended up looking like this:
SELECT eventdate, events
FROM ataglance
WHERE (eventdate = DATE())

4

Whoops. Accidentally submitted too early.

ANYWAY, that working code is what I thought I had already tried, but I must have been doing something wrong.

NEW QUESTION, though –

My database results include a date field. However, I can only get to display in the short date mode – 10/9/2003. I’ve changed the format for this field in the actual table in my Access database to Long Date (Thursday, October 9, 2003), but it still displays as short in the database results.

So is there some SQL coding that would force it to show the Long date? Or is there something else that would work or a setting I’m not finding?

Anyone?

And thanks again, Hauky. I may have more questions for you in the future.

5

use
FormatDateTime(date,1) in your ASP code.

You can also use any of the following for the second parameter:

[ul]
[li]0 - Display a date and/or time. If there is a date part, display it as a short date. If there is a time part, display it as a long time. If present, both parts are displayed. [/li][li]1 - Display a date using the long date format specified in your computer’s regional settings.[/li][li]2 - Display a date using the short date format specified in your computer’s regional settings.[/li][li]3 - Display a time using the time format specified in your computer’s regional settings.[/li][li]4 - Display a time using the 24-hour format (hh:mm).[/li][/ul]

6

JamesCarroll, i hate to be a nuisance. That code is what I’ve found elsewhere on the web. Here’s the problem, though:

The date that I want formatted that way is dynamically drawn from a database. So where do I put that code?

The SQL script that ultimately worked, I enter into a window within Frontpage. I have limited experience with directly editing the HTML, but I have done some of it. Can you tell me where to insert the Date formatting code?

Thanks for any additional light you can shed on the situation.

7

Ok I’m gonan try to do this easy, hope you understand…

Somewhere in your code you are setting variables, and elsewhere you are writing them. You need to format your variable before you write it.

For example, setting variables would be like:

EventDate = oRS(“EventDate”)

Where EventDate is the variable name and oRS(“EventDate”) is saying “the value of the field EventDate for the recordset oRS”

When you then write the variable to display in your HTML, you’re either doing <%=EventDate%> or Response.Write EventDate (same thing).

BEFORE you write out the Date, you need to format it as such. So you need to do it probably right under the line where you set the variable:

EventDate = oRS(“EventDate”)
If EventDate <> “” Then
EventDate = FormatDateTime(EventDate, 1)
End If
The If/Then it to keep the FormatDateTime func from getting pissed if there’s no data.

The above code will then make EventDate into long date, and when you write out EventDate it will be as you formatted it, not as it is in the DB.

There’s a few other methods to doing this but that should explain it. Hopefully. Get into your code and see what’s happening, it’s the best way to learn.

Happy programming!