3 Replies Latest reply on Sep 19, 2006 11:27 AM by Dan Bracuk

    Insert multiple entries for calendar

    EmmanuelRuiz Level 1
      I am inserting CREATEDATE and DUEDATE which could be 9/18/06 & 9/22/06 into a DB as one single entry. I would like a way to calculate the amount of days between 9/18/06 and 9/22/06 and be able to insert the same amount of entries as the amount of days between these dates so that it shows as follows:
      Row1: Entry 9/18/06
      Row2: Entry 9/19/06
      Row2: Entry 9/20/06
      Row2: Entry 9/21/06
      Row2: Entry 9/22/06

      So that I have 5 unique entries into the DB as oppose to just one.

      Thanks
      Emmanuel
        • 1. Re: Insert multiple entries for calendar
          Dan Bracuk Level 5
          First, use cf to create a list of all the dates you want to insert. Second, loop through the list and insert your data..

          There are a variety of ways to do each step. The best method for step 2 is db specific.
          • 2. Re: Insert multiple entries for calendar
            EmmanuelRuiz Level 1
            For anybody interested I ended up setting up the following which works fine:


            <cfset mydate1=DateFormat(arguments.DUEDATE,"yyyy,m,dd hh:mm:ss")>
            <cfset mydate2=DateFormat(arguments.LESSONDATE,"yyyy,m,dd hh:mm:ss")>
            <Cfset intervaldays = "#Int(mydate2-mydate1)#">
            <cfset correctamountofdays = #REreplace(intervaldays,"-","","All")#>


            <cfloop index="LoopCount" from="0" to="#correctamountofdays#">

            <Cfset datevar = DateAdd("d", "#LoopCount#", "#mydate2#")>

            <cfquery datasource="#application.dsn#" name="updateLesson">
            insert into
            aip.LESSONPLANS
            (SUBJECT,LESSONDATE,OBJECTIVE,STANDARD,ASSIGNMENT,STRATEGY,ASSESSMENT,CREATEDATE,DUEDATE, TID,MODIFICATIONS,COLOR)
            values
            ( <cfqueryparam value="#arguments.SUBJECT#" cfsqltype="CF_SQL_VARCHAR">,
            <cfqueryparam value="#datevar#" cfsqltype="cf_sql_timestamp">,
            <cfqueryparam value="#arguments.OBJECTIVE#" cfsqltype="CF_SQL_VARCHAR">,
            <cfqueryparam value="#arguments.STANDARD#" cfsqltype="CF_SQL_VARCHAR">,
            <cfqueryparam value="#arguments.ASSIGNMENT#" cfsqltype="CF_SQL_VARCHAR">,
            <cfqueryparam value="#arguments.STRATEGY#" cfsqltype="CF_SQL_VARCHAR">,
            <cfqueryparam value="#arguments.ASSESSMENT#" cfsqltype="CF_SQL_VARCHAR">,
            SYSDATE,
            <cfqueryparam value="#datevar#" cfsqltype="cf_sql_timestamp">,
            <cfqueryparam value="#session.lessonPlanTID#" cfsqltype="CF_SQL_VARCHAR">,
            <cfqueryparam value="#arguments.MODIFICATIONS#" cfsqltype="CF_SQL_VARCHAR">,
            <cfqueryparam value="#arguments.color#" cfsqltype="CF_SQL_VARCHAR">)
            </cfquery>
            </cfloop>
            • 3. Re: Insert multiple entries for calendar
              Dan Bracuk Level 5
              Not sure about this
              <Cfset intervaldays = "#Int(mydate2-mydate1)#">

              If mydate1 was 2006-09-18 23:59
              and mydate2 was 2006-09-19 00:01

              Would intervaldays be 0 or 1?