Welcome to WebmasterWorld Guest from 107.23.176.162

Forum Moderators: ocean10000

Message Too Old, No Replies

Calendar and display information

     
1:43 pm on Jun 21, 2010 (gmt 0)

Preferred Member

10+ Year Member

joined:Nov 24, 2005
posts:429
votes: 0


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...
2:51 pm on June 21, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Feb 1, 2005
posts:732
votes: 0


In Your SQL add something like this:

WHERE MONTH(DATE_TODAY) = 6
3:23 pm on June 21, 2010 (gmt 0)

Preferred Member

10+ Year Member

joined:Nov 24, 2005
posts:429
votes: 0


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.
3:38 pm on June 21, 2010 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5806
votes: 155


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
3:43 pm on June 21, 2010 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5806
votes: 155


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())
4:03 pm on June 21, 2010 (gmt 0)

Preferred Member

10+ Year Member

joined:Nov 24, 2005
posts:429
votes: 0


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"
4:30 pm on June 21, 2010 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5806
votes: 155


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)? :)
5:19 pm on June 21, 2010 (gmt 0)

Preferred Member

10+ Year Member

joined:Nov 24, 2005
posts:429
votes: 0


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.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members