Dates with day suffix in ColdFusion ORM
I often need to display dates where the day has a suffix such as 1st, 2nd, 3rd, 4th. This is a pain to do in CFML as ColdFusion's DateFormat doesn't support it. However, all is not lost, I've just discovered that MYSQL has a DATE_FORMAT function which does support it. You can use it like so:
select DATE_FORMAT( article_date, '%W, %D %M %Y' ) from Articles
Which will return something like:
Tuesday, 12th July 2011
As I use ColdFusion ORM for my applications, I wanted to be able to use this feature of MySQL inside my entites. This is where computed properties come into play. Computed properties allow you to write custom SQL statements which are then used to populate the property. here is what my Article.cfc looks like:
component persistent="true" table="ARTICLES"
{
/* PROPERTIES
-------------------------------------------------------------- */
property name="articleid" column="article_id" fieldtype="id" generator="native";
property name="title" column="article_title" type="string";
property name="content" column="article_content" type="string";
// computed properties
property name="datePublished"
formula="select DATE_FORMAT(article_date, '%W, %D %M %Y') from ARTICLES a where a.article_id = article_id";
}
The only tricky part is that you want the formula to only return one record, and clearly, it needs to be the date for the current Article. To do that you'll need to alias the table name and refer to the id prefixed with the alias in your where claus. This makes a bit more sense when you see the the SQL that Hibernate generates under the hood.
select
this_.article_id as article1_260_0_,
this_.article_title as article2_260_0_,
this_.article_content as article3_260_0_,
(select
DATE_FORMAT( this_.article_date,
'%W, %D %M %Y' )
from
ARTICLES a
where
a.article_id = this_.article_id) as formula3_0_
from
ARTICLES this_
where
this_.article_section=?
and this_.article_active=?
order by
this_.article_date desc
Now, in your view you can simply output the nicely formatted date using the generated getter. For example:
<cfoutput>
#MyArticle.getdatePublished()#
</cfoutput>
The only downside is that you do need to be using MySQL to do this and I haven't found a way to do it Microsoft SQL Server.
- Posted in:
- ColdFusion
- Hibernate
besides that you also get duration formatting ("2 days from now") and all the i18n goodies you can shake a stick at.
Comment by PaulH – July 12, 2011
www.cflib.org/udf/GetOrdinal
and skip going to the db at all.
Comment by Raymond Camden – July 12, 2011
@CFJediMaster (and Adobe Evangelist no less), that's what I have been doing, but I find it annoying having to inject a helper library into my objects and/or views so this is just seems kinda clean :)
Comment by John Whish – July 12, 2011
Funny how the same feature has such radically different opinions with different people. :)
Comment by Raymond Camden – July 12, 2011
Of course I could in a view just do #HelperLibrary.nicedate( Content.getDate() )#, but then I need to do that on every view that shows the date (such as a listing, search results etc)., so it's nice to be able to just do #Content.getNiceDate()# instead.
The 3rd option is to have a super class which has the GetOrdinal method in it that my Content cfc extends and I happy with that, but then I can't use that method elsewhere. (I guess this is your preferred option?).
As always, pros and cons. The cool thing is ColdFusion lets you can do either or all :)
Please tell me if I'm talking complete nonsense! :)
Comment by John Whish – July 12, 2011
But yeah - if you wanted to use it in N CFCs, you would need to (well, you should) inject it. That's a bit more verbose and I'd maybe then go back to what you have here.
Comment by Raymond Camden – July 12, 2011
Comment by John Whish – July 12, 2011
It's not really the topic but as you are talking about helpers injection into object or view...
Personally I use the "getPageContext().getFusionContext().hiddenScope" structure in which I insert a new element (generally the key is the character "$") who is an object that contains all my UDF's or other helper objects.
At each request start, I had to re-insert this object into the hidden scope.
With this, I can access anywhere to, for example, $.getNiceDate();
For your example, a function like this may solve your problem:
public string function datePublished() {
return $.getNiceDate( getDate() );
}
( Assuming that the property 'date' exists )
( John, it's the first time I post here but I want to say you that I like your blog and your interesting post! )
Comment by Loic Mahieu – July 12, 2011
Thanks for reading my blog!
Comment by John Whish – July 13, 2011
Or, could be bad ass and do it in Javascript... I've done that too! :D
Comment by Jake "Master of the Universe" Hendy – July 20, 2011
Comment by WPS – March 06, 2012