I am trying to customize some code and seem to be having a problem with a CF function called CreateODBCDateTime. In my Access database I have a date/time field called EventDate. It is accepting dates/times from a form in the following format;
3/19/2008 6:00:00 AM
I don't want to change this format because another part of the application requires it. Part of my app has a mini calendar grid that when loaded on a page checks that EventDate field, and if an event exists shows a link on the mini calendar that then links through to the main calendar for the app. The problem I am having is in this bit of code: <cftry> <cfset newDate = CreateODBCDate(CreateDate(SESSION.cYear, SESSION.cMonth, theDay))> <cfset trigger = 1> <cfcatch> <cfset trigger = 0> </cfcatch> </cftry>
NewDate is a variable that is used to query the Events table. The above function tries to create a date object. If it is successful, a trigger variable is set to 1. The problem is in my database the EventDate field is set to a date/time field (not just a date) so the CreateODBCDate never successfully creates an object. If I change that function to CreateODBCDateTime, it wants me to add variables for hours, minutes and seconds. How do I create these variables so they will coincide with how my database field is structured? I have tried setting variables for hours, minutes and seconds and then changing the function to CreateODBCDateTime but have not been successful. Below is the remainder of the query code;
<cfif trigger eq 1> <cfquery datasource="caldb" name="checkEvent"> select * from Event where EventDate = #newDate# </cfquery> <!-- If there are some events highlight the backgound of a date cell --> <cfif checkEvent.recordcount GT 0> <cfset bgrd = "CCCCCC"> </cfif> </cfif>
You were probably not using the createDateTime function when you added the values for hour, minute, and second.
You can replace the numbers in the code below with your variable names but this is the syntax for creating an odbc datetime string for 03/25/2008 at 1:52:32 pm:
Here is what I had previously that didn't seem to work.
First, I set the following variables for hours, minutes and seconds: <cfset theHour = Hour(Now())> <cfset theMins = Minute(Now())> <cfset theSecs = Seconds(Now())>
Then I changed the function to CreateODBCDateTime and added the variables to the method as below:
This didn't seem to work? How do I get the hours, minutes and seconds in my database field and those in the object created in the above code to be structured so that the object is successfully created? I don't think I am setting the variables for them correctly?
Changing the Seconds() function to Second() eliminated the errors, but now the mini-calendar is showing links for ALL dates on the calendar, not just ones listed in the EventDate field. I'm wondering if the Now() function is not the correct way to set these variables?
Next, I commented out the cftry/cfcatch code and re-ran the page, and I got the following error: ********************************** Date value passed to date function CreateDateTime is unspecified or invalid. Specify a valid date in CreateDateTime function.
The error occurred in index1.cfm: line 193
191 : trigger is set to 1 --> 192 : <!---<cftry>---> 193 : <cfset newDate = CreateODBCDateTime(CreateDateTime(SESSION.cYear, SESSION.cMonth, theDay, theHour, theMins, theSecs))> 194 : <!---<cfset newDate = CreateODBCDate(CreateDate(SESSION.cYear, SESSION.cMonth, theDay))>---> 195 : <cfset trigger = 1> ********************************** Any thoughts?
That error indicates the values SESSION.cYear, SESSION.cMonth, theDay, theHour, theMins, theSecs do not translate to a valid date. I would output the values of those variables onto the screen so you can make sure they are all numeric values, and that they are valid.
And, Session.startDay is defineds as <cfset SESSION.startDay = DayOfWeek(CreateDate(SESSION.cYear,SESSION.cMonth,1))>
Not sure where to go from here, when I leave the function as CreateODBCDate and take the hours, mins, and seconds out of a few of the EventDate fields it all works fine?
Sorry I haven't posted back, been out sick. As I mentioned I am trying to customize some code for a calendar app, so I'm still figuring parts of this out as well. Here is the code for that entire page.....the pos variable and the Session.startDay variables are initially defined at the top of the page: ****************Start************************** <!--
Calendar has three atributes a user has to define. The required attribute is a name of a cfm template in which cf_calendar tag is inserted. For example, if you want to insert this tag into index.cfm page you have to specify this page as a custom tag attribute: page = "index.cfm"
There are also two optional attributes that allow to specify ColdFusion datasource and table name where you keep information about events. If you do not specify these attributes they are set up as "calendar" datasource and "calendar" table name by default. If you use your own datasource be sure that your event table has the following required fields:
id (integer autoincrement) cdate (date/time general date format in MSAccess) ctitle (text field) cdescription (memo field)
To navigate through the calendar a URL parameter URL.mLink is used. If left arrow clicked, the page in which the calendar is loaded and based on the URL.mLink parameter the calendar shifts itself either one month ahead (URL.mLink is 2 ) or one month back (URL.mLink is 1 ). By default this parameter is equal zero and calendar is set up to the current month.
<!-- When the right arrow clicked, the calendar is set up to one month forward from the current month --> <cfif URL.mLink is 2> <cfset SESSION.cMonth = SESSION.cMonth+1> <cfif SESSION.cMonth GT 12> <cfset SESSION.cYear = SESSION.cYear+1> <cfset SESSION.cMonth = 1> </cfif> <cfset SESSION.nDays = DaysInMonth(CreateDate(SESSION.cYear, SESSION.cMonth,1))> <cfset SESSION.startDay = DayOfWeek(CreateDate(SESSION.cYear, SESSION.cMonth,1))> </cfif> <cfset pos = 1>
<!-- Calendar styles is a subject to customization -->
<!-- To output a calendar the tabular format is used. The next code generates 7 x 8 HTML table. First row is allocated for the calendar navigation (month link). Second row is week days header, and the rest are cells allocated to display day numbers -->
<tr align="center" valign="middle" bgcolor="0C0F57"> <td width="20" height="20"><span class="style9">Su</span></td> <td width="20" height="20"><span class="style9">Mo</span></td> <td width="20" height="20"><span class="style9">Tu</span></td> <td width="20" height="20"><span class="style9">We</span></td> <td width="20" height="20"><span class="style9">Th</span></td> <td width="20" height="20"><span class="style9">Fr</span></td> <td width="20" height="20"><span class="style9">Sa</span></td> </tr> <cfloop index ="i" from="1" to="6"> <tr> <cfloop index ="j" from="1" to="7"> <cfset theDay = pos - (SESSION.startDay-1)> <!-- NewDate is a variable that will be used to query Events table. Here the calendar tries to create a date object. In case of success a triger variable trigger is set to 1 --> <cftry> <cfset newDate = CreateODBCDate(CreateDate(SESSION.cYear, SESSION.cMonth, theDay))> <cfset trigger = 1> <cfcatch> <cfset trigger = 0> </cfcatch> </cftry>
<!-- If newDate is a date when an event(s) happen then we link this date in the calendar to events.cfm page where events for this date will be populated. Otherwise the date in the calendar will by dysplayed without the link and the highlighted background. This query starts if trigger variable is set to 1 that means that date is correct -->
<cfset bgrd = "FCFAE9">
<!-- bgrd defines a background color of the cell. The idea is that if the calendar has any events at this date, the cell background changes its color. You can customize backgrounf color as it fits the color gamma of your web page -->
<cfif trigger eq 1> <cfquery datasource="caldb" name="checkEvent"> select * from Event where EventDate = #newDate# </cfquery> <!-- If there are some events we highlight the backgound of a date cell --> <cfif checkEvent.recordcount GT 0> <cfset bgrd = "CCCCCC"> </cfif> </cfif>
<!-- If the date in the right range than it is dysplayed --> <cfif NOT (theDay LT 1 OR theDay GT SESSION.nDays)>
<cfif trigger eq 1> <cfif checkEvent.recordcount GT 0> <a href="quick_add_event_show.cfm?m1=#SESSION.cMonth#&y1=#SESSION.cYear#&d1=#theDay#" target="_blank"><span class="style6">#theDay#</span></a> <cfelse> <span class="style6">#theDay#</span> </cfif> </cfif> <!-- Otherwise, the hidden dash is inserted in order to dysplay the table borders correctly --> <cfelse> <span class="style1">-</span> </cfif>
</td> <cfset pos = pos +1> </cfloop> </tr> </cfloop> </table> </cfoutput> ****************End**************************** Again, If I change the EventTime field in my database to be just dates this works fine, but my database is set to time stamp the Event as well, which I need it do for other reasons. I'm trying to change the CreateODBCDate function to CreateODBCDateTime and somehow define variables for hours, mins and seconds that will trigger the newDate variable to create a date/time object when it queries the database.
