Problem with DateDiff and Date/Time value.

Report · Sep 11, 2009

The form below displayed perfectly until I added the line for DateDiff between VacStart and VacEnd. I receive an error message;

An error occurred while evaluating the expression:

#DateDiff("w", "VacStart", "VacEnd")#

Error near line 48, column 51.

Parameter 2 of function DateDiff which is now "VacStart" must be a date/time value

The error occurred while processing an element with a general identifier of (#DateDiff("w", "VacStart", "VacEnd")#), occupying document position (48:50) to (48:86).

I have tried changing the DB value to Date/Time as well as Text and get the same result each time. Any help would be greatly appreciated. Here is my code:

<cfquery datasource="manna_premier" name="TM_log">
SELECT *
FROM TMStatusLog
</cfquery>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>TM Status Log</title>

</head>

<body>

<p align="center" class="style4">Territory Manager Status Log </p>
<table width="1448" border="0">
<tr>
    <th width="71"><span class="style3">ID No.#</span></th>
<th width="88"><span class="style3"> Date</span></th>
<th width="166"><span class="style3">TM Name</span></th>
<th width="129"><span class="style3">Status</span></th>
<th width="139"><span class="style3">Vac Start </span></th>
<th width="137"><span class="style3">Vac End</span></th>
<th width="203">Ttl Days </th>
<th width="203"><span class="style3">DSR Ride Along Name</span></th>
<th width="274"><span class="style3">Service Call Name</span></th>
</tr><cfoutput query="TM_log">
<tr>
    <td><span class="style3">#ID#</span></td>
    <td><div align="center"><span class="style3">#DateFormat(LogDate, "mm/dd/yyyy")#</span></div></td>
    <td><div align="center"><span class="style3">#TerritoryManager#</span></div></td>
    <td><div align="center"><span class="style3">#Status#</span></div></td>
    <td><div align="center"><span class="style3">#DateFormat(VacStart, "mm/dd/yyyy")#</span></div></td>
    <td><div align="center"><span class="style3">#DateFormat(VacEnd, "mm/dd/yyyy")#</span></div></td>
<cfif VacStart IS NOT "Null">
    <td><div align="center"><span class="style3">#DateDiff("w", "VacStart", "VacEnd")#</span></div></td>
</cfif>
    <td><div align="center"><span class="style3">#DSRName#</span></div></td>
    <td><div align="center"><span class="style3">#ServiceName#</span></div></td>
</tr></cfoutput>
</table>

</body>
</html>

Report · Sep 11, 2009

first off lose the quotes around your vars.

#DateDiff("w", VacStart, VacEnd)#

Report · Sep 11, 2009

I made the changes you suggested and am now getting a new error message:

Error Occurred While Processing Request

Error Diagnostic Information

An error occurred while evaluating the expression:

#DateDiff("w", VacStart, VacEnd)#

Error near line 47, column 48.

Parameter 2 of function DateDiff which is now "" must be a date/time value

The error occurred while processing an element with a general identifier of (#DateDiff("w", VacStart, VacEnd)#), occupying document position (47:47) to (47:79).

Any other suggestions? Thanks!

Report · Sep 11, 2009

Error Diagnostic Information
An error occurred while evaluating the expression:
#DateDiff("w", VacStart, VacEnd)#
Error near line 47, column 48.

Parameter 2 of function DateDiff which is now "" must be a date/time value

The error occurred while processing an element with a general identifier of (#DateDiff("w", VacStart, VacEnd)#), occupying document position (47:47) to (47:79).
Any other suggestions? Thanks!

Well... what does the error message say?
Parameter 2 of function DateDiff which is now "" must be a date/time value
What does this tell you? What is parameter 2 of that function call? It's VacStart. So it's saying VacStart is an empty string, and it's saying an empty string is not a valid value for that parameter.
So the question you need to ask yourself is whether you expect VacStart to be an empty string? Or do you perhaps expect it to be a date? I would guess you're expecting it to be a date. So the question becomes... Why is VacStart an empty string?
--
Adam

Report · Sep 11, 2009

Ah yes...that makes sense. The vacation dates are only filled in if a vacation has been selected so there will be empty strings. I added a cfif statement to deal with the empty strings and the page displays correctly now. Now, the question you have to ask yourself is just how condescending you intended your post to be. Were you going for highly condescending? I felt it was highly condescending so you were right on the mark if that was your aim. LOL...thanks for helping a rookie!

Report · Sep 11, 2009

I didn't mean it to sound condescdending at all. I was merely trying to demonstrate that if one starts by looking through the error message analyse what the error message says, work out what the error message means... that's often all one has to do to work out what a problem is.

By working through my suggestions, you were able to work out what the problem was. Hopefully using that "technique" will stick with you and when you have similar problems again (because - face it - all of us always do end up doing this sort of thing all the time), it might help you getting to the bottom of the issue faster. I think showing how to work something out is a better approach that just saying "this is the answer", because no-one learns anything from that.

I will never - or at least very very rarely - just give someone the answer to their questions. Sorry.

--

Adam

Report · Sep 12, 2009

I apologize Adam, I obviously misread the 'tone' of your email. And I certainly cannot argue your logic as you are correct that your assistance helped me find the answer on my own. To be honest I couldn't fault you had you been condescending as I am sure it must get old having to answer a lot of the same stuff over and over again. Sorry for the ribbing and thanks again for your assistance!

Report · Sep 13, 2009

NP. I am actually condescending sometimes on these forums if someone rubs me up the wrong way, but it was not the case here (at least not on purpose!).

Take it easy.

--

Adam

Report · Sep 13, 2009

Add validation right after the line <cfoutput query="TM_log">, for data that will go into functions. Do something like

<cfset isLogDateADate = isDate(logDate)>
<cfset isVacStartADate = isDate(vacStart)>
<cfset isVacEndADate = isDate(vacEnd)>

<cfif NOT isLogDateADate>
    <cfset logdate = now()>
</cfif>
<cfif NOT isVacStartADate>
    <cfset vacStart = now()>
</cfif>
<cfif NOT isVacEndADate>
    <cfset vacEnd = now()>
</cfif>

Adobe Community

Problem with DateDiff and Date/Time value.

Error Occurred While Processing Request

Error Diagnostic Information

Error Diagnostic Information