12 Replies Latest reply: May 25, 2012 6:15 AM by goodychurro1 RSS

    Set a date two days in the future

    goodychurro1 Community Member

      Hi all

      This code works fine to set a value if there is no value in form.MEETING (so I can insert the value into my db with sql):

       

      <cfset isMEETINGNull = iif(len(trim(form.MEETING)) EQ 0, true, false)>

       

      How can I set the value to a date two days from now if it is empty(instead of null as it is at the moment, seomthing like this?)

       

      <cfset isMEETINGNull = DateFormat(DateAdd("d", +2, Now()), "MM/DD/YYYY")>

      Thank you!

       

      Message was edited due to google research possible answer

        • 1. Re: Set a date two days in the future
          Dan Bracuk Community Member

          if (len(trim(form.meeting)) > 0

          theDate = dateAdd("d", 2, now();

          else

          theDate = something.

           

          But it's not that simple.  You might also have to ensure that form.meeting actually is a date and if so, that it meets whatever other criteria you have.  Finally, don't use dateformat in this situation.  It's a display function that returns a string.  You want to send a date object to your db.

          • 2. Re: Set a date two days in the future
            BKBK CommunityMVP

            Here is yet another suggestion. It includes validation.

             

            <cfset meetingDate = trim(form.meeting)>

            <cfif not isDate(meetingDate)>

            <cfset meetingDate = dateAdd("d", 2, now())>

            </cfif>

             

            The value to store in the database is then meetingDate.

            • 3. Re: Set a date two days in the future
              goodychurro1 Community Member

              Thanks BKBK I put this code but I get an error:

              <!--- UPDATE QUERY --->

              <cfif isDefined("form.UPDATEADDBTN")>
              <cfif FORM.ENTID GTE 1>
              <cfset isCOMMENTSNull = iif(len(trim(form.COMMENTS)) EQ 0, true, false)>
              <cfset meetingDate = trim(form.meeting)>
              <cfif not isDate(meetingDate)>
              <cfset meetingDate = dateAdd("d", 2, now())>
              </cfif>

              <CFQUERY name="updatecompany" datasource="salesdb">
              update COMPANY
              SET COMMENTS= <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.COMMENTS)#"

              null="#isCOMMENTSNull#" />,
                 MEETING= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#trim(form.MEETING)#"

              null="#isMEETINGNull#" />,
                 WHERE ENTID = #FORM.ENTID#
              </CFQUERY>
              <cfelse>

               

               

              Error:

              Variable ISMEETINGNULL is undefined.

              75 :    MEETING= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#trim(form.MEETING)#" null="#isMEETINGNull#" />,

              • 4. Re: Set a date two days in the future
                BKBK CommunityMVP

                I said, "The value to store in the database is then meetingDate". You yourself say, implicitly, at least, that there will be no null meetingDates. I therefore expected to see

                 

                MEETING = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#meetingDate#"/>

                • 5. Re: Set a date two days in the future
                  goodychurro1 Community Member

                  Ok thanks BKBK, I changed it to meetingDate:

                   

                    MEETING= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#trim(form.MEETING)#"

                  null="#meetingDate#" />

                   

                  I now get this error:

                  Cannot convert the value of type class coldfusion.runtime.OleDateTime to a boolean

                   

                  75 :    MEETING= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#trim(form.MEETING)#" null="#meetingDate#" />,

                   

                   

                  In my database MEETING is a varchar field, length 255, decimals 0 and allow null. Default: NULL, character set: utf8

                  I tried to change it to timestamp in my mysql program but it doesn't let me (maybe because I already have value in that field? I can delete the column and create it again there if required)

                  • 6. Re: Set a date two days in the future
                    Dan Bracuk Community Member

                    Whatever you have to do to change that field from varchar to timestamp, do it.

                    • 7. Re: Set a date two days in the future
                      goodychurro1 Community Member

                      Thanks Dan, I have now changed the MEETING field in the db to:

                      type: timestamp

                      length: 0

                      decimals: 0

                      allow null

                      default: NULL

                       

                       

                      I get this error in coldfusion?:

                      Cannot convert the value of type class coldfusion.runtime.OleDateTime to a boolean

                      • 8. Re: Set a date two days in the future
                        BKBK CommunityMVP

                        goodychurro1 wrote:

                         

                        Ok thanks BKBK, I changed it to meetingDate:

                         

                        No, you didn't!

                         

                        MEETING= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#trim(form.MEETING)#"

                        null="#meetingDate#" />

                         

                        I said nothing about nulls. Here it is again: MEETING = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#meetingDate#"/>

                        • 9. Re: Set a date two days in the future
                          BKBK CommunityMVP

                          I wouldn't go changing many things at once. That would lead to complexity. Start with your original code, and just tweak it a little, using the suggestions. For example

                           

                          <!--- UPDATE QUERY --->

                          <cfif isDefined("form.UPDATEADDBTN")>

                              <cfif FORM.ENTID GTE 1>

                                  <cfset isCOMMENTSNull = iif(len(trim(form.COMMENTS)) EQ 0, true, false)>

                                  <cfset meetingDate = trim(form.meeting)>

                                  <cfif not isDate(meetingDate)>

                                      <cfset meetingDate = dateAdd("d", 2, now())>

                                  <cfelse>

                                      <!--- Here, apply Dan's advice to convert from string to datetime object. Use, for example, parseDatetime, createdate or createdatetime --->

                                      <cfset meetingDate = parseDatetime(meetingDate)>

                                  </cfif>

                           

                              <CFQUERY name="updatecompany" datasource="salesdb">

                                  update COMPANY

                                  SET COMMENTS = <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#trim(form.COMMENTS)#" null="#isCOMMENTSNull#" />,

                                  MEETING = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#meetingDate#" />

                                  WHERE ENTID = <cfqueryparam cfsqltype="cf_sql_integer" value="#FORM.ENTID#" />

                              </CFQUERY>

                          <cfelse>

                          • 10. Re: Set a date two days in the future
                            Dan Bracuk Community Member

                            Regarding, "

                            I get this error in coldfusion?:

                            Cannot convert the value of type class coldfusion.runtime.OleDateTime to a boolean"

                             

                            What is the line of code creating this error?

                            • 11. Re: Set a date two days in the future
                              BKBK CommunityMVP

                              Dan Bracuk wrote:

                               

                              Regarding, "

                              I get this error in coldfusion?:

                              Cannot convert the value of type class coldfusion.runtime.OleDateTime to a boolean"

                               

                              What is the line of code creating this error?

                              The error was caused by the attribute null="#meetingDate#". I had suggested that this attribute be left out altogether.

                              • 12. Re: Set a date two days in the future
                                goodychurro1 Community Member

                                Thanks for the help it works great