Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at CFUNITED 2010 (Landsdown, VA) with:

Ask Ben: Displaying A Simple Event Calendar Month With ColdFusion

By Ben Nadel on

I am trying to display a calendar month in my application. I have all the events in a database (SQL Server). I don't want to show the individual events on this view, only to highlight the days that have any events. Right now, for each day I am querying the database for events on that day. This is slow and seems not optimal. How can I better do this?

Yes this is not optimal. Think about it this way, if you query the database for each day, you might have to go back to the database like 30+ times JUST to display this calendar. No need to do this, but first, before I show you anything, I have to take some things for granted; I am going to assume that you have an event table with some sort of ID, a start date, and an end date. I am sure you have much more than that, but this is really all we are going to need for your calendar display.

To test with, I am creating and manually populating a query:

  • <cfquery name="qEvent" datasource="#REQUEST.DSN.Source#">
  • DECLARE
  • @event TABLE (
  • id INT,
  • date_started DATETIME,
  • date_ended DATETIME
  • )
  • ;
  •  
  •  
  • INSERT INTO @event (
  • id,
  • date_started,
  • date_ended
  • )(
  • SELECT
  • 1,
  • <cfqueryparam value="#Now()#" cfsqltype="CF_SQL_TIMESTAMP" />,
  • <cfqueryparam value="#Now() + 5#" cfsqltype="CF_SQL_TIMESTAMP" />
  •  
  • UNION ALL
  •  
  • SELECT
  • 2,
  • <cfqueryparam value="#CreateDateTime( 2007, 04, 15, 0, 0, 0 )#" cfsqltype="CF_SQL_TIMESTAMP" />,
  • <cfqueryparam value="#CreateDateTime( 2007, 04, 15, 23, 59, 59 )#" cfsqltype="CF_SQL_TIMESTAMP" />
  •  
  • UNION ALL
  •  
  • SELECT
  • 3,
  • <cfqueryparam value="#CreateDateTime( 2007, 04, 8, 10, 30, 0 )#" cfsqltype="CF_SQL_TIMESTAMP" />,
  • <cfqueryparam value="#CreateDateTime( 2007, 04, 10, 4, 45, 0 )#" cfsqltype="CF_SQL_TIMESTAMP" />
  •  
  • UNION ALL
  •  
  • SELECT
  • 4,
  • <cfqueryparam value="#CreateDateTime( 2007, 04, 4, 12, 0, 0 )#" cfsqltype="CF_SQL_TIMESTAMP" />,
  • <cfqueryparam value="#CreateDateTime( 2007, 04, 5, 12, 0, 0 )#" cfsqltype="CF_SQL_TIMESTAMP" />
  •  
  • UNION ALL
  •  
  • SELECT
  • 5,
  • <cfqueryparam value="#CreateDateTime( 2007, 04, 29, 9, 0, 0 )#" cfsqltype="CF_SQL_TIMESTAMP" />,
  • <cfqueryparam value="#CreateDateTime( 2007, 04, 29, 12, 0, 0 )#" cfsqltype="CF_SQL_TIMESTAMP" />
  • );
  •  
  •  
  • SELECT
  • *
  • FROM
  • @event
  • ;
  • </cfquery>

The code above is not really relevant, I just wanted you to see where I was getting my data.

Now that that is taken care of, let's talk a little bit about what goes into a calendar. First off, a calendar doesn't just show you the current month. It might also show you a bit of the last month and bit of the next month (depending on which days of the week this month starts and ends). Therefore, we don't really care about this month exclusively; what we really care about is this "calendar" month, by which I mean the actual date range that would appear on this page of a printed calendar. Keep that in mind.

Next, I just wanted to talk for a second about date/time objects and numbers. What you may or may not know is that date/time objects are actually floating point values that represent the number of days that have passed since a given point in time (which is different across different systems). Of this floating point value, the integer is the day and the decimal is the time (which is really just fractions of day).

Once you embrace the fact that date/time objects can be used like numbers, adding days and looping over days becomes as simple as doing math and iterating over an index loop. What you also have to realize is that ColdFusion will convert a string date value into a numeric date value when you try to do math with it. You don't have to do any explicitly conversion. It's really pretty awesome that way.

That being said, here is the code that will take the above query and build the calendar:

  • <!---
  • Get the month that we are going to be showing the
  • events for (April 2007).
  • --->
  • <cfset dtThisMonth = CreateDate( 2007, 4, 1 ) />
  •  
  • <!---
  • Because the calendar month doesn't just show our
  • month - it may also show the end of last month and
  • the beginning of next month - we need to figure out
  • the start and end of the "calendar" display month,
  • not just this month.
  • --->
  • <cfset dtMonthStart = (dtThisMonth + 1 - DayOfWeek( dtThisMonth )) />
  •  
  • <!--- Get the last day of the calendar display month. --->
  • <cfset dtMonthEnd = (dtThisMonth - 1 + DaysInMonth( dtThisMonth )) />
  • <cfset dtMonthEnd = (dtMonthEnd + (7 - DayOfWeek( dtMonthEnd ))) />
  •  
  •  
  • <!---
  • ASSERT: At this point, not only do we know what month
  • we are going to display, we also know the first and last
  • calendar days that are going to display. We do not need
  • to know what numeric month those actually fall on.
  • --->
  •  
  •  
  • <!---
  • Create an object to hold the dates that we want to
  • show on the calendar. Since our calendar view doesn't
  • have any real detail other than event existence, we
  • don't have to care about event details. We will use
  • this struct to create an index of date DAYS only.
  • --->
  • <cfset objEvents = StructNew() />
  •  
  •  
  • <!---
  • Let's populate the event struct. Here, we have to
  • be careful not just about single day events but also
  • multi day events which have to show up more than once
  • on the calendar.
  • --->
  • <cfloop query="qEvent">
  •  
  • <!---
  • For each event, we are going to loop over all the
  • days between the start date and the end date. Each
  • day within that date range is going to be indexed
  • in our event index.
  •  
  • When we are getting the date of the event, remember
  • that these dates might have associated times. We
  • don't care about the time, we only care about the
  • day. Therefore, when we grab the date, we are Fixing
  • the value. This will strip out the time and convert
  • the date to an integer.
  • --->
  • <cfset intDateFrom = Fix( qEvent.date_started ) />
  • <cfset intDateTo = Fix( qEvent.date_ended ) />
  •  
  •  
  • <!---
  • Loop over all the dates between our start date and
  • end date. Be careful though, we don't care about days
  • that will NOT show up on our calendar. Therefore,
  • using our are Month Start and Month End values found
  • above, we can Min/Max our loop.
  •  
  • When looping, increment the index by one. This will
  • add a single day for each loop iteration.
  • --->
  • <cfloop
  • index="intDate"
  • from="#Max( intDateFrom, dtMonthStart )#"
  • to="#Min( intDateTo, dtMonthEnd )#"
  • step="1">
  •  
  • <!---
  • Index this date. We don't care if two different
  • event dates overwrite each other so long as at
  • least one of the events registers this date.
  • --->
  • <cfset objEvents[ intDate ] = qEvent.id />
  •  
  • </cfloop>
  •  
  • </cfloop>
  •  
  •  
  • <cfoutput>
  • <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
  • <html>
  • <head>
  • <title>Calendar Month</title>
  •  
  • <style type="text/css">
  •  
  • body,
  • td {
  • font: 11px verdana ;
  • }
  •  
  • table.month {}
  •  
  • table.month tr.dayheader td {
  • background-color: ##8EDB00 ;
  • border: 1px solid ##308800 ;
  • border-bottom-width: 2px ;
  • color: ##E3FB8E ;
  • font-weight: bold ;
  • padding: 5px 0px 5px 0px ;
  • text-align: center ;
  • }
  •  
  • table.month tr.day td {
  • background-color: ##E3FB8E ;
  • border: 1px solid ##999999 ;
  • color: ##308800 ;
  • padding: 5px 0px 5px 0px ;
  • text-align: center ;
  • }
  •  
  • table.month tr.day td.othermonth {
  • background-color: ##F3FDD0 ;
  • color: ##999999 ;
  • }
  •  
  • table.month tr.day td.event {
  • background-color: ##C8F821 ;
  • color: ##666666 ;
  • font-weight: bold ;
  • }
  •  
  • </style>
  • </head>
  • <body>
  •  
  • <h2>
  • #DateFormat( dtThisMonth, "mmmm yyyy" )#
  • </h2>
  •  
  • <table width="100%" cellspacing="2" class="month">
  • <colgroup>
  • <col width="12%" />
  • <col width="15%" />
  • <col width="15%" />
  • <col width="15%" />
  • <col width="15%" />
  • <col width="15%" />
  • <col width="13%" />
  • </colgroup>
  • <tr class="dayheader">
  • <td>
  • Sun
  • </td>
  • <td>
  • Mon
  • </td>
  • <td>
  • Tus
  • </td>
  • <td>
  • Wed
  • </td>
  • <td>
  • Thr
  • </td>
  • <td>
  • Fri
  • </td>
  • <td>
  • Sat
  • </td>
  • </tr>
  • <tr class="day">
  •  
  • <!---
  • Now, we need to loop over the days in the
  • calendar display month. We can use the start
  • and end days we found above. When looping, add
  • one to the index. This will add a single day
  • per loop iteration.
  • --->
  • <cfloop
  • index="intDate"
  • from="#dtMonthStart#"
  • to="#dtMonthEnd#"
  • step="1">
  •  
  •  
  • <!---
  • Check to see which classes we are going to
  • need to assign to this day. We are going to
  • use one class for month (this vs. other) and
  • one for whether or not there is an event.
  • --->
  • <cfif (Month( intDate ) EQ Month( dtThisMonth))>
  • <cfset strClass = "thismonth" />
  • <cfelse>
  • <cfset strClass = "othermonth" />
  • </cfif>
  •  
  • <!---
  • Check to see if there is an event scheduled
  • on this day. We can figure this out by checking
  • for this date in the event index.
  • --->
  • <cfif StructKeyExists( objEvents, intDate )>
  • <cfset strClass = (strClass & " event") />
  • </cfif>
  •  
  • <td class="#strClass#">
  • #Day( intDate )#
  • </td>
  •  
  •  
  • <!---
  • Check to see if we need to start a new row.
  • We will need to do this after every Saturday
  • UNLESS we are at the end of our loop.
  • --->
  • <cfif (
  • (DayOfWeek( intDate ) EQ 7) AND
  • (intDate LT dtMonthEnd)
  • )>
  • </tr>
  • <tr class="day">
  • </cfif>
  •  
  • </cfloop>
  • </tr>
  • </table>
  •  
  • </body>
  • </html>
  • </cfoutput>

Running the above code, we get the following page:


 
 
 

 
Simple Event Calendar Month Display Using ColdFusion  
 
 
 

There you go. Of course, you can make your calendar display more complicated than that, such as adding in a link for each day. What's VERY important to understand is that we are keeping our dates very consistent across this example. We are only ever dealing with the DATE part of a date/time stamp; we are never referencing the time even though time is recorded for our events. We Fix() all of the dates that have times (this chops off the decimal which removes the time part of the date/time stamp). Then, once we have date-only date/time stamps, we are careful to add only whole days to it. It is only through this kind of consistency that we are able to take advantage of the objEvents indexing.




Reader Comments

I have seen similar examples but your explanation is really great.
thanks for all detail info; please keep it up.

@Sana,

Thanks for the kind words.

@Ben,

This example does have events that span multiple days. Look as the query that is being populated at the top. Notice that in several of the cases the date_started value and the date_ended value do not fall on the same day.

Ben - it's posts like this one that make our blog one of the top 2 or 3 CF blogs on the web. Your code, comments, and explanations are just awesome!

Ben - the "our blog" above should of course be "your blog." (must have been wishful thinking on my part ;)

@Bruce,

Thanks a lot man. That really means a lot to me. I am glad to help people out. Please let me know if you ever want something written up or demoed or whatnot.

Sorry if this is a duplicate comment, but I think this is the better thread to pose the problem on:

What if you want to use this logic, but start the week on Monday and end it on Sunday?

Is there someway to reassign the dayOfWeek so that Monday = 1 and Sunday = 7? Would it work if I were to create my own shifted Array for this, say, shiftedDayOfWeek() and always wrap dayOfWeek() inside of it?

So shiftedDayOfWeek(dayOfWeek(dateProducingMonday)) would produce 1. Sort of a dirty way to override the dayOfWeek() function.

No sweat. I tried for a while to come up with the right wrapper. What I was missing was the "mod 7" and, generally, your quick wit!

Thanks!

I'd like to tell you that coming up with that MOD formula was cinch :) But it wasn't. It was mostly trial an error. Part of the problem is just visualizing it and then coming up with the repeating sequence. MOD is totally awesome, but it's not always intuitive to me how to make the sequence I see in my head.

I would like to have a repeating patter of colors assigned to the numbers of the days.

I am a fire fighter by trade. I work every other day for five days ( 24 hour shifts, some nights we sleep and some nights we wish we could sleep ) and then have four days off.

For example I would work monday wednesday and then friday, have four days off and start again working wednesday, friday and then sunday, four days off etc etc. This comes out to be a repeating event every nine days.

We have 3 shifts (ABC) each shift has a color code, A is Green, B is Red and C is Blue.

So B shift works the 2, 4, 6, 11, 13, 15, 20, 22, 24, 29 of June, all those numbers would be red on the calendar.

C shift works 1,3,8,10,12,17,19,21,26,28,30 of June so those numbers on the calendar would be blue.

A Shift works 5, 7, 9, 14, 16, 18, 23, 25, 27 of June so those days would be green on the calendar.

How could I implement this same functionality on your calendar. I would like to list some events on each day and have them be a hyperlink to a detail page about the event.

Thanks for any help with this.

Jim

@Jim,

First off, thanks for being a fire fighter - a truly heroic profession! Much respect.

Secondly, we have to not only consider the pattern, but also when it starts. Do we start from the first of each month? Or, does the cycle begin based on when it ended the previous month... ie. Does Shift C always start on the 1st? Or only the 1st of June?

Help me figure out that part and I will definitely help you with the display.

@Jim

Wow!! What an awesome scenario!

So, you fight fires AND write CF code? I mean, it says a lot about ColdFusion that a firefighter prefers it!

Ben,

First off, THANK YOU for an awesome blog, I am trying to learn CF as fast as I can cram stuff into my little brain, but you know us fireman, put us in a round room with no windows or doors with two steel balls, we will break one and loose the other. This is coming from a reformed truckie, I worked on a ladder company for many years, now I am on an engine company. Heck my knuckles are almost healed.

Regarding the date we would have a start date of somewhere in the past such as 10/1/2001. This date would need to be hard coded so to speak as the seed date. This rotation is perpetual in that in keeps going and going forwards and backwards. We would need to be able to provide a way to show both directions i.e. not just adding but subtracting days to get the right pattern as well.

Our shift pattern is actually called a modified Detroit schedule. The actual shift pattern is CACBCBABA. Check out this link to see an actual printed calendar. http://www.ladder54.com/schedule.htm
If we look at October 1st then follow the pattern of CACBCBABA you will see what I mean, I work the red days, all 110 to 112 days a year at 24 hours a shift. We would need to trap for leap years as well all though I don't think that is much of a problem, I could be wrong though.

Thanks for the support; it is nice to see such quick help. I am new to this and if I can develop a solution for our problem, the actual events would be pulled from our FireRMS (our fire inident reporting system). There is a scheduler module that is built in and we kind of use it backwards from normal use. We use a calendar to show which shift is working and on the same day show who is of on vacation, Kelly day, sick leave etc. This is a sql database with a foxpro front end.

If we can get the display figured out for this I will have a boat load of different calendars to show, like a staffing calendar with details for each person (number of hours of sick leave, vacation, comp time hours left), a reservation calendar for the conference/training rooms, the number of calls per day grouped with a detail page, a calendar that shows scheduled training, and equipment maintenance due, a calendar of schedule inspections, the possibilities are endless. With regards to the vacation calendar I would like to be able to have the end user click a link to a submit vacation request, this could be done from how as IT will not let us into the network except for a web based email application called Groupwise from Novel.

Groupwise has a calendar module, like Outlook does, but you cannot show this on the internet let alone let us interact with it by submitting vacation etc. It does not allow for events from a database to be displayed on it like you can do with CF

My problem is not just getting the calendar display down but making the events show up from a database of existing events. I have seen calendars by doing a coldfusion calendar google search but they do not address the items I need. This is really a full fledged application in itself.

Thanks again for the help

Jim

Ben,

I just have to say that you are an awesome developer. I have been using CF for about five years, and am always learning something new and intuitive from your blog(s). Please keep up the excellent work!!

John

Wow. I'm so glad I found your blog. I'm fairly new to CF, and have a quick question. If I wanted to add an event title to the events on your calendar, how would I go about it?

Hands down the best calendar, explanation, and coding I have seen! Thank you very much for taking the time to share your knowledge with everybody else!

@Todd,

First off, thanks for the kind words - that means a lot to me. This calendar simply display whether or not the calendar has ANY events on a given day. If you want to get more complicated and actually show the events on a given day, you might want to checkout the new series of posts I am doing on calendaring, starting here:

http://www.bennadel.com/blog/877-ColdFusion-Calendar-Event-Scripting-Inspired-By-Peter-Bell.htm

This, while more complicated, goes into how to display more information per day.

incredible post--concise explanation! been puzzling over how to do this exact same thing during downtime in my day job! thanks so much. dan

Looks great, Ben. I'm going to give it a shot for a project I'm working on. Thanks for all those great articles you've posted, by the way.

-Hamlet

Ben. You are my hero. I could have coded this myself but my code most likely would have been as long if not longer than yours and yours is mostly comments.