Author: Steven Neiland
Published:

Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

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.

Related Blog Postings

What Do You Think?

Reader Comments

Post a Comment

Comment Etiquette:

  • Please keep comments on-topic.
  • Please do not post unrelated questions or large chunks of code.
  • Please do not engage in flaming/abusive behaviour.
  • Comments that contain or appear to be advertisments, will not be published.
  • Comments that appear to be created for the purpose of linkbuilding to commercial sites will be removed.

We are all adults here so play nice.

*
*



Archives Blog Listing

Tag Listing

Learn CF In A Week

Treehouse

 
Fork me on GitHub