Date processing with Access in CF

Some folks have had problems doing date processing within SQL against an Access database. (Let's not get into whether anyone should be choosing Access as a DB. For some low volume sites, it works adequately.)

Part of the date handling problem stems from the fact that Access may need help determining that what you're passing it is a date (versus a number). There are two ways to do this. You could use the CreateODBCDate() function to convert a date to a format that will be passed from CF to SQL as an ODBC Formatted date.

Another is to take advantage of the fact that Access can use pound signs around a date to indicate that the value is a date. Of course, those are the same pound signs that CF uses for variables, which can complicate things.

First, just know that one way to format a SQL statement WITHIN Access to process, for instance, records between two dates (leave CF out of the picture for a moment) would be to render it as:

SELECT StartDate FROM Employee WHERE StartDate between #01/01/97# and #01/01/98#

If you wanted to do that within a CFQUERY statement, then you'd need to escape those pound signs (double them) so that CF didn't think you were trying to refer to variables, as in:

<CFQUERY NAME="test" DATASOURCE="CompanyInfo">
SELECT StartDate FROM Employee WHERE StartDate between ##01/01/97## and ##01/01/98##
</CFQUERY>

Of course, you could also convert this to use the ODBCDateFormat function, as in:

<CFQUERY NAME="test" DATASOURCE="CompanyInfo">
SELECT StartDate FROM Employee WHERE StartDate between #ODBCDateFormat("01/01/97")# and #ODBCDateFormat("01/01/98")#
</CFQUERY>

Notice again that the use of this function doesn't require the use of escaped pound signs because the function creates the date in a format Access understands. But you'll usually have the date coming in as a variable. You could then easily change the ODBCDateFormat as in:

<cfset date="01/01/98">
<CFQUERY NAME="test" DATASOURCE="CompanyInfo">
SELECT StartDate FROM Employee WHERE StartDate between #ODBCDateFormat("01/01/97")# and #ODBCDateFormat(date)#
</CFQUERY>

But if you tried to use the Access form of passing pound-delimited dates, such as in the first two examples above, and you wanted to use a variable for one of them, you'd then need 3 pound signs around that (two for the escaped pound for Access and one for the pound needed to refer to the variable/function), as in:

<cfset date="01/01/98">
<CFQUERY NAME="test" DATASOURCE="CompanyInfo">
SELECT StartDate FROM Employee WHERE StartDate between ##01/01/97## and ###date###
</CFQUERY>

Finally, note that if you wanted to use the now() function in that last example for the date (which returns the current date AND time), or if your incoming "date" variable had minutes and seconds in it, you'd need to wrap it in a dateformat function before using the "pound sign" approach to date formatting in Access because that doesn't like anything but a date to be passed in, as in:.

<CFQUERY NAME="test" DATASOURCE="CompanyInfo">
SELECT StartDate FROM Employee WHERE StartDate between ##01/01/97## and ###dateformat(now())###
</CFQUERY>

Note, too, that I've not bothered with any date formatting "mask". In my testing, Access is happy with the default dateformat form of date layout.

So when should you use the pound sign approach? Well, really, it's more something that's needed within Access itself to be able to detect that a string of numbers (and dashes or slashes) are in fact representing a date. The thing is, with the ODBCDateFormat function (and CF's Now() returning a date in ODBCDateTime Format), it's perhaps not as important to use the pound-surrounded approach to dates in Access.

Wow. A Contribute DesDev Site Already

Wow, there's already a DesDev site devoted to Contribute, with 23 articles. Check it out at http://www.macromedia.com/desdev/contribute/.

More Thoughts on Contributor's value for CFers

There's a lot of talk today in various forums about the value and impact of Contributor for CF (and other web app developers). As I started to say in my previous entry, I think it will take time to sort out the impact not only for the clearly intended audience (folks needing simple content management systems, and developers who previously did updates for that audience), as well as the impact on the content management business (don't know that it will be a category killer, but it could in some segments).

But there's also the question of the impact for general CF (and other web app) developers.

While it may seem that the product is geared toward static sites, it does indeed support dynamic sites (it will automatically lock such server-side code to prevent the user editing it). And they suggested in the announcement conference call that it will change even more regarding that sort of development environment.

But I've been thinking about it in a way that perhaps may seem unorthodox to some. Many may even disagree. I'd also argue that it's possible that many sites that are currently dynamic could be switched to being more static. For instance, many use server-side coding simply to facilitate reusable/moderately changeable aspects of interface design (nav bars, page layout, reusable content segments). While Dreamweaver MX's template feature could be used for some of this, many web app developers prefer to do such things the old fashioned way (in CF, using CFINCLUDE and coordinating the upload of that code themselves). With DWMX, they would need to "republish"
the site whenever they'd change the template, rather than simply changing the included file. And while you could maybe put that power into the client's hands, they would have to have DWMX.

Contribute could change that sort of scenario, bringing the power of DWMX's templating feature to alleviate the page being dynamic just for these reusable components, without the user having to have DWMX.

This doesn't diminish CF's place (or whatever server-side coding one may use). Instead, it limits it to being used only for things that truly are dynamic (database, component, or web server-generated info, for instance). This could have a performance impact by allowing pages that are currently CF (or ASP/JSP/PHP) templates (just for the reusable interface aspects) to be reset to static pages.

But another reason server-side coding is often used is simply to facilitate creation/editing of content in a portion of a page. This, of course, is another thing that Contribute would alleviate entirely.

Still, the current lack of an approval/workflow system will keep some from jumping at the opportunity. It's seems the challenge is how to solve that adequately for all preferences. I can see how that would be a dilemma.

Again, time will tell both in how people find ways to best use it as well as how the product may evolve.

Look into Macromedia's new tool announced today, Contributor

Could I be the first to blog the link to Macromedia's new product announced today, Contributor. Check it out at http://www.macromedia.com/software/contribute/. I'm sure other bloggers will have much more to say. Of course, some will argue that it's not really something related to CF since it's really just a way to facilitate end-user updating of predominantly static sites.

But it could be used with CF sites, and more important there may be aspects of some sites you build in CF that could perhaps be set back to "static" by enabling update access in Contributor. Also, it has built-in ability to lock out dynamic code like CF (which can be disabled if you desire), so in time it will become more clear how indeed even CFers may find it quite appropriate to use.

One thing to note: the price listed on the FAQ page off that site shows it being $99, but it's not clarified that it's $99 for a client installation of Contribute (in other words, for a contributor), but according to a conference call announcing the product this morning, you can "contribute" to any number of sites from that one license. But this does mean that if you want dozens of people to be contributors, they'd each need a license. See the site for more details (which will hopefully answer any questions we'd all have over time)