In this article, I will walk you through how to use the DateDiff function in SQL Server and ColdFusion to calculate differences in dates as well as how to achieve the same results using HQL.

Table of Contents

Scenario

On my blog landing page, I want to show the top popular posts. In order to accomplish this, I need to change my outer query to sort the results by the most popular blog posts.

My blog currently stores the blog date and the total number of views for each post. To get the most popular posts, I need to take the total number of posts and divide it by the total number of months since the post was made. This will allow me to sort the results by the number of average number of monthly views for each post.

I will show you how to do this in SQL Server, and ColdFusion, and perform the necessary calculations to arrive at this value when using the Hibernate Query Language.

Calculating the Elapsed Number of Months Between Two Dates Using SQL Server

Here, we will take the total number of views and divide it by the number of months that have elapsed since the post was made using SQL Servers DateDiff function. We will use this value to sort our query by the most popular blog posts. I am not going to elaborate much here as using SQL Servers DateDiff function is a pretty common approach. The only thing that I want to clarify is that I am adding one to the value as I don't want to have a divide-by-zero error and it works beautifully for new posts.

<cfquery name="Data" datasource="#dsn#">
	SELECT 
		Title,
		DatePosted,
		NumViews,
		dateDiff(Month, DatePosted,getDate()) as monthsSincePost,
		NumViews/(dateDiff(Month, DatePosted,getDate())+1) as viewsPerMonth 
	FROM Post
	ORDER BY NumViews/(dateDiff(Month, DatePosted,getDate())+1) DESC
</cfquery>

Calculating the Elapsed Number of Months Manually When Using HQL

Unfortunately, the Hibernate Query Langauge, or HQL, does not have a built-in DateDiff function. However, there are other HQL date functions that we can use to manually calculate the differences between two dates. 

Here again, we will take the number of total views and divide it by the number of months that have elapsed to get the most popular posts. But in this example, we need to use the following formula to find how many months have elapsed between two dates. 

Our formula to determine the number of months elapsed will be:

month(Current Date) - month(Date Posted) + 12 * (year(Current Date) - year(Date Posted))

Put another way in plain English:

The current month, as an integer, minus the month that the post was made, as an integer, plus 12 times (the current year minus the year that the post was made)

Here is our HQL query:

<cfquery name="Data" dbtype="hql">
	SELECT new map (
		Title as Title, 
		NumViews as NumViews,
		DatePosted as DatePosted,
		month(DatePosted) as monthDatePosted,
		month(current_date()) as monthCurrentDate,
		year(DatePosted) as yearDatePosted,
		year(current_date()) as yearCurrentDate,
		NumViews/(month(current_date())-month(DatePosted)+12*(year(current_date())-year(DatePosted))+1) as AverageMonthlyViews
	)  
	FROM Post
	ORDER BY NumViews/(month(current_date())-month(DatePosted)+12*(year(current_date())-year(DatePosted))+1) DESC
</cfquery>

Using the ColdFusion Built-In DateDiff Function

As we have just seen, determining the difference in years or months between two dates using manual calculations works OK, trying to determine the difference in days or hours between two dates will get enormously complex. In this case, it is probably better to use the DateDiff function of your favorite language.

In this example, we will query the database to obtain the date posted and the total number of views and use ColdFusion to perform the calculations using the DateDiff function. This query will not be sorted the way that we want as we are performing the calculations after the HQL query has been made. However, in this example, we are going to replicate the original Java HashMap table using a ColdFusion array of structures and then sort the data using a function found in the eminently useful open-source CFlib code repository.

<cfquery name="Data" dbtype="hql">
	select new map (
		Title as Title, 
		0 as MonthsSincePost,
		NumViews as NumViews,
		0 as AverageMonthlyViews,
		DatePosted as DatePosted
	)  
	FROM Post
</cfquery>

<!--- Loop through the Data object --->
<cfloop from="1" to="#arrayLen(Data)#" index="i">
	<!--- Extract the total number of views and the months that have elapsed since the post was made --->
	<cfset numViews = Data[i]["NumViews"]>
	<cfset MonthsSincePost = dateDiff("m", Data[i]["DatePosted"], now())>
	<!--- If a month has elapsed, divide the number of views by the number of months since the post. Use the ceiling function to round up to the nearest integer --->
	<cfif MonthsSincePost>
		<cfset MonthlyViews = ceiling((numViews/MonthsSincePost) / 30)>
	<cfelse>
		<!--- If the post was not more than a month old, use the number of views --->
		<cfset MonthlyViews = numViews>
	</cfif>
	<cfif isDebug>
		<cfoutput>
			DatePosted: #Data[i]["DatePosted"]#<br/> 
			NumViews: #numViews#<br/>
			MonthsSincePost: #MonthsSincePost#<br/> 
			MonthlyViews: #MonthlyViews#<br/>
		</cfoutput><br/>
	</cfif>
	<cfset Data[i]["MonthsSincePost"] = MonthsSincePost>
	<cfset Data[i]["AverageMonthlyViews"] = MonthlyViews >
</cfloop>

<!--- Sort the array of structures using a lib found at https://cflib.org/udf/ArrayOfStructsSort --->
<cfset sortedData = arrayOfStructsSort(Data, 'AverageMonthlyViews', 'desc', 'numeric')>

<cfdump var="#sortedData#">

ColdFusion Function to Sort an Array of Structures Similiar to a Java HashMap

We can use the following function to sort an array of structures, or even a Java HashMap returned from a HQL query. Be sure to use the numeric argument when sorting numeric values, otherwise strange results can occur.
 
<cfscript>
/**
 * Sorts an array of structures based on a key in the structures.
 * 
 * @param aofS      Array of structures. (Required)
 * @param key      Key to sort by. (Required)
 * @param sortOrder      Order to sort by, asc or desc. (Optional)
 * @param sortType      Text, textnocase, or numeric. (Optional)
 * @param delim      Delimiter used for temporary data storage. Must not exist in data. Defaults to a period. (Optional)
 * @return Returns a sorted array. 
 * @author Nathan Dintenfass (nathan@changemedia.com) 
 * @version 1, April 4, 2013 
 */
function arrayOfStructsSort(aOfS,key){
        //by default we'll use an ascending sort
        var sortOrder = "asc";        
        //by default, we'll use a textnocase sort
        var sortType = "textnocase";
        //by default, use ascii character 30 as the delim
        var delim = ".";
        //make an array to hold the sort stuff
        var sortArray = arraynew(1);
        //make an array to return
        var returnArray = arraynew(1);
        //grab the number of elements in the array (used in the loops)
        var count = arrayLen(aOfS);
        //make a variable to use in the loop
        var ii = 1;
        //if there is a 3rd argument, set the sortOrder
        if(arraylen(arguments) GT 2)
            sortOrder = arguments[3];
        //if there is a 4th argument, set the sortType
        if(arraylen(arguments) GT 3)
            sortType = arguments[4];
        //if there is a 5th argument, set the delim
        if(arraylen(arguments) GT 4)
            delim = arguments[5];
        //loop over the array of structs, building the sortArray
        for(ii = 1; ii lte count; ii = ii + 1)
            sortArray[ii] = aOfS[ii][key] & delim & ii;
        //now sort the array
        arraySort(sortArray,sortType,sortOrder);
        //now build the return array
        for(ii = 1; ii lte count; ii = ii + 1)
            returnArray[ii] = aOfS[listLast(sortArray[ii],delim)];
        //return the array
        return returnArray;
}	
	
public array function arrayOfStructsSort2(required array aOfS, required string key, string sortOrder = "asc", sortType = "textnocase", string delim = ".") {
//make an array to hold the sort stuff
var sortArray = [];
//make an array to return
var returnArray = [];
//grab the number of elements in the array (used in the loops)
var count = arrayLen(aOfS);
//make a variable to use in the loop
var ii = 1;
//loop over the array of structs, building the sortArray
if (sortType == "numeric"){
for(ii = 1; ii <= count; ii++)
sortArray[ii] = NumberFormat(aOfS[ii][key],".____")*10000 & delim & ii;
} else {
for(ii = 1; ii <= count; ii++)
sortArray[ii] = aOfS[ii][key] & delim & ii;
}
//now sort the array
arraySort(sortArray,sortType,sortOrder);
//now build the return array
for(ii = 1; ii <= count; ii++)
returnArray[ii] = aOfS[listLast(sortArray[ii],delim)];
//return the array
return returnArray;
}
</cfscript>