ColdFusion DateDiff - Is there a better way?

Question

I simply want to combine the total years of service from various members of my team. The below script works fine, however, the hire date for each member does not pull from a database. Which means I have to hard code each persons hiredate and then do a DateDiff on them. With a team of 28 members that would cause a code management nightmare.

My main goal is to make the code simpler and more efficient, especially when managing 28 team members. If part of the path requires adding the user's hiredate to a database; I can do that. I just see the code below needing a rewrite, I just don't know the best method.

Is there a better way?

<cfscript>
  REQUEST.rightnow = Now();
  dateBob = DateDiff("yyyy", "1996 02 01", REQUEST.rightnow);
  dateSam = DateDiff("yyyy", "1996 08 01", REQUEST.rightnow);
  dateJoe = DateDiff("yyyy", "2004 12 01", REQUEST.rightnow);
  dateJohn = DateDiff("yyyy", "2001 01 01", REQUEST.rightnow);
  combinedDate = (dateBob + dateSam + dateJoe + dateJohn);
</cfscript>
<cfoutput>
  #combinedDate# total years<br>
  #dateBob# years<br>
  #dateSam# years<br>
  #dateJoe# years<br>
  #dateJohn# years<br>
</cfoutput>

--- EDIT ---

Per Matt's suggestion as a database query; something like this?

SELECT hire_date,
        ROUND((DATEDIFF(NOW(),hire_date)) / 365) AS dateDiff
FROM members
WHERE hire_date IS NOT NULL

--- EDIT 2 ---

I just realised I forgot to add SUM() of all the "dateDiff". If anyone uses this SQL you will have to hand it off to ColdFusion, I suppose. Unless I overlooked a way to do it in SQL?

--- EDIT 3 ---

Per Leigh's comments of encouragement; I found a better way. The TIMESTAMPDIFF() MySQL function adds a unit variable that can be day, month, year, etc. This allowed me to remove the / 365 and the ROUND(). Which in turn allowed me to use SUM() and not get a syntax error from MySQL. I also renamed my alias from dateDiff to just Dif because I wanted to avoid confusion with DATEDIFF().

See below. :D

SELECT hire_date,
        SUM(TIMESTAMPDIFF(YEAR,hire_date,NOW())) AS Dif
FROM members
WHERE hire_date IS NOT NULL

Not really. I mean, you could load the names as keys and their dates as values in a struct and loop over the sturct to make the code simpler. But it would essentially do the same thing. Putting it in a DB would essentially do that anyway. — Leeish, Jun 16 '15 at 20:20
if you put this in a database it could all be done with a SQL query — Matt Busche, Jun 16 '15 at 20:34
*something like this?* Do not roll your own date logic. Most all databases have date functions - use them :-) Exact syntax is database specific, so please update the question tags to indicate your DBMS. — Leigh, Jun 16 '15 at 21:34
*(Edit) Thanks. Do a search on date logic/expression.. lots of threads on SO. For example, take a look at [this thread](http://stackoverflow.com/a/1383580/104223) for logic on calculating years difference. To find total years for all employees, just wrap it in a `SUM`. *RE: that would cause a code management nightmare.* Only if you are not storing data in a database where it belongs ;-) — Leigh, Jun 16 '15 at 21:51
*you will have to hand it off to ColdFusion, I suppose* No, you do not. Databases have aggregate functions like `SUM` function as well. It is kind of SQL 101, so if you are not familiar with them, you should read up on the [MySQL docs](https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_sum) and/or review a tutorial on aggregate SQL functions. — Leigh, Jun 16 '15 at 22:02
If you are going to divide by 365, may as well make it 365.25. — Dan Bracuk, Jun 16 '15 at 23:38
@Grimdari - You should post the solution as an "answer" (that is allowed on Stack Overflow) so it is more visible and folks can vote on it too. — Leigh, Jun 17 '15 at 03:35

Grimdari · Accepted Answer · 2015-06-17T23:27:29.507

1

The solution to improving the code posted for ColdFusion was actually replacing it. By adding the hire_dates of each member to an existing MySQL database and writing the query below; I no longer needed all of the ColdFusion code in my original post.

SELECT SUM(TIMESTAMPDIFF(YEAR,hire_date,NOW())) AS Dif
FROM members
WHERE hire_date IS NOT NULL

edited Jun 17 '15 at 23:27

answered Jun 17 '15 at 14:53

Grimdari

353
1
16

If you only want an overall total, get rid of the first "hire_date," reference. It is not needed. In fact the syntax above would cause a syntax error in some db's because the first column is not part of an aggregate function. However, MySQL is a bit more lenient about that kind of thing. – Leigh Jun 17 '15 at 18:52
Updated! Thank you once again, Leigh! – Grimdari Jun 17 '15 at 23:27

ColdFusion DateDiff - Is there a better way?

1 Answers1