calculate number of days between date fields 1

[dv8er88]

I have 2 date fields in Mysql. start_date and complete_date.

How do I calculate the amount of days between them.

Example:
start_date is 2005-01-28
complete-date is 2005-07-28

I want to output Completion Time 6 days or 7 days what ever it is?

Thanks

 

[peterswan]

This works good:


<cfset start_date = "1/28/2005">
<cfset complete_date = "7/28/2005">

<cfoutput>
The difference bewtween the start date and the complete date is:
<br>
#dateDiff("d", start_date, complete_date)# days
</cfoutput>

Cheers,

Peter

 

[r937]

if you want simply to display the date difference in rows that you have already selected and returned into coldfusion, then there's no harm in using coldfusion's date functions

but often you will want to select rows based on the difference

in this case, you do not want to return the entire table (thousands of rows?) just to do the calculation in coldfusion to find out there's only a dozen rows that qualify

that's when it's important to do the calculation in mysql

select foo, bar
  from yourtable
 where to_days(complete_date)
     - to_days(start_date) < 10

r937.com | rudy.ca