Calendar and display information

[hal12b]

I have a page where I want to display events only for the current month and want this to be dynamic. I don't want to have to manually change anything each month. So for example, I have a table with the following fields

DATE_TODAY
TITLE
DESCRIPTION

I want the page to only list items for June at the moment. So the output would look like

6/11/10 - some information...
6/21/10 - some information...
6/21/10 - some information...
6/21/10 - some information...
6/23/10 - some information...
6/24/10 - some information...
etc...

Right now I am only able to display everything. How can I set up the database query to only display information for a particular month?

Thanks
I'm learning as I go...

[marcel]

In Your SQL add something like this:

WHERE MONTH(DATE_TODAY) = 6

[hal12b]

Yeah, but I don't want to manually change this to 7 next month, then 8, etc... I may have figured it out. I am using

Public strcurrmonth = Today.ToString("MM")

strQuery = "SELECT * FROM tblCalendar where DATE_DISPLAY > '" & strcurrmonth & " /01/10' AND DATE_DISPLAY < '" & strcurrmonth & "/30/10' "


I just need to account for months without 30 days. I am working on this now.

[LifeinAsia]

You're part of the way there by getting the current month. What you want to do next is create "StartDate," a DATETIME variable for the first day of the current month (how you do it depends on your database). Then you want to add one month to that date (again, the specific function depends on your DB), call it "EndDate." Then you want to find all events between StartDate inclusive and EndDate exclusive:
SELECT *
FROM tblCalendar
WHERE Date_Display >= StartDate AND Date_Display < EndDate

[LifeinAsia]

Or, assuming that your Date_Display field is DATETIME and building upon what marcel said, something like the following should work with MS SQL:
SELECT *
FROM tblCalendar
WHERE Month(Date_Display)=DatePart(m,getdate()) AND YEAR(Date_Display)=DatePart(yy,getdate())

[hal12b]

What I have might be archaic and I believe the only issue will be the leap year in February. This is what I did.

Public strcurrmonth = Today.ToString("MM")
Public strdaysinmonth

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load




'might have to be updated for February's leap year.
If strcurrmonth = 1 Then
strdaysinmonth = 31

ElseIf strcurrmonth = 2 Then
strdaysinmonth = 28

ElseIf strcurrmonth = 3 Then
strdaysinmonth = 31

ElseIf strcurrmonth = 5 Then
strdaysinmonth = 31

ElseIf strcurrmonth = 6 Then
strdaysinmonth = 30

ElseIf strcurrmonth = 7 Then
strdaysinmonth = 31

ElseIf strcurrmonth = 8 Then
strdaysinmonth = 31

ElseIf strcurrmonth = 9 Then
strdaysinmonth = 30

ElseIf strcurrmonth = 10 Then
strdaysinmonth = 31

ElseIf strcurrmonth = 11 Then
strdaysinmonth = 30

ElseIf strcurrmonth = 12 Then
strdaysinmonth = 31

Else

End If


and the query is -->
strQuery = "SELECT * FROM tblCalendar where DATE_DISPLAY > '" & strcurrmonth & " /01/10' AND DATE_DISPLAY < '" & strcurrmonth & "/" & strdaysinmonth & "/10' ORDER BY [DATE_DISPLAY] DESC"

[LifeinAsia]

You'll also need to remember to change your query every year (otherwise, you'll only show 2010 events).

Question 1: Is Date_Display a DATETIME field?
Question 2: Is there any reason you're opposed to using our solutions (that don't have a leap year issue or require annual changes)? :)

[hal12b]

I'll need to revisit yours. I had finished what I had then read your post. Thanks though. Let me give it shot... and thanks for pointing out #1.