In previous jobs I used Coldfusion to do my date math for me in my data tables. However in my current job we make use of the cfgrid tag. For those of you who dont know the cfgrid tag is an ajax powered table that takes a query object as its datasource. This creates the limitation that you cannot take two dates from the database table and have coldfusion do date math on them to get your final result (ie time remaining between a target date and today).

However SQL server provides some helpful date math functions which make it is possible to do date math at the query stage instead of using coldfusion. This is especially useful when dealing with cfgrid type controls.

Days Remaining Until Target

For example let try to get the number of days remaining between today and a stored target date in the database.

To get the days remainig until a target date we make use of two date functions.

• GETDATE(): Which returns the current date (as SQL Server knows it so make sure your clock is set correctly).
• DATEDIFF(datepart, startdate, enddate): Which returns the difference between 2 dates

Below we see how the combination of these two functions gives us the target date from the DB, todays date, and the difference between the two in days.

SELECT 
   targetDate,
   getdate() AS today,
   datediff("day",getdate(),targetdate) as daysRemaining
FROM
   projectTable
--snip--

Test

To test our outputs we use the following code.

<!---Get the target date, todays date and the difference between the two --->
<cfquery name="test" datasource="#SQLServerDatasource#">
  SELECT 
     targetDate,
     getdate() AS today,
     datediff("day",getdate(),targetdate) as daysRemaining
  FROM
     projectTable
  --snip--
</cfquery>

<cfoutput query="test">
<p>
    On today #dateformat(test.today,"dd-mmm-yyyy")# <br/>
    we have #test.daysremaining# days remaining<br/>
    until #dateformat(test.targetdate,"dd-mmm-yyyy")
</p>
</cfoutput>

The returns us the following output.

On today 21-aug-2010
we have 10 days remaining
until our deadline 31-aug-2010

While this is a simple example you can see how datemath in sqlserver can be used for many more complex equations.