8 Replies Latest reply on Sep 13, 2009 7:02 AM by BKBK

    Problem with DateDiff and Date/Time value.

    emartek1

      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>

        • 1. Re: Problem with DateDiff and Date/Time value.
          tooMuchTrouble Level 3

          first off lose the quotes around your vars.

           

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

          • 2. Re: Problem with DateDiff and Date/Time value.
            emartek1 Level 1

            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!

            • 3. Re: Problem with DateDiff and Date/Time value.
              Adam Cameron. Level 5

               

               

              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

              • 4. Re: Problem with DateDiff and Date/Time value.
                emartek1 Level 1

                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!

                • 5. Re: Problem with DateDiff and Date/Time value.
                  Adam Cameron. Level 5

                  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

                  • 6. Re: Problem with DateDiff and Date/Time value.
                    emartek1 Level 1

                    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!

                    • 7. Re: Problem with DateDiff and Date/Time value.
                      Adam Cameron. Level 5

                      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

                      • 8. Re: Problem with DateDiff and Date/Time value.
                        BKBK Adobe Community Professional & MVP

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

                         

                        <cfoutput query="TM_log">

                        <!--- Test for valid date data --->
                        <cfset isLogDateADate = isDate(logDate)>
                        <cfset isVacStartADate = isDate(vacStart)>
                        <cfset isVacEndADate = isDate(vacEnd)>

                         

                        <!--- My example: all dates default to now --->
                        <cfif NOT isLogDateADate>
                            <cfset logdate = now()>
                        </cfif>
                        <cfif NOT isVacStartADate>
                            <cfset vacStart = now()>
                        </cfif>
                        <cfif NOT isVacEndADate>
                            <cfset vacEnd = now()>
                        </cfif>