9 Replies Latest reply on Oct 16, 2007 9:47 AM by cf_dev2

    Problem with between date ranges

    siriiven Level 1
      I have to generate and insert a record for each day in a series of date ranges. I am passign some values from a form and trying to insert them for each day.

      Here is my code:

      <cfquery name="datetest" datasource="ds">

      SELECT '#form.building#' AS BldgArea, #form.semcode# AS Semester, DateAdd('d', DaysSpan.Counter, '#form.firstday#') AS SchedDt, DaysSpan.Counter AS Expr1
      FROM DaysSpan
      WHERE ((DateAdd('d', DaysSpan.Counter, '#form.firstday#')) <= '#form.lastday#')
      </cfquery>

      So I have a table with a a field called counter which has rows 0 - 366. I want to get a firstday and increment that date by 1 until it reaches the lastday then stop.

      I run the page and i am not getting any errors, but nothing is being output. When I take the where clause out it works fine and output 365 days of dates starting with my firstday. I am only going by semester though so I only want the days in between firstday and lastday output.

      Any ideas?
        • 1. Re: Problem with between date ranges
          Dan Bracuk Level 5
          Your post does not clearly describe what you are trying to accomplish. Are you attempting to insert a series of records starting with the form.firstday and ending with form.lastday?
          • 2. Re: Problem with between date ranges
            siriiven Level 1
            Yes that is what I am trying to do. I am going to be adding a schedule in for each day of a semester. The semester firstday and lastday are passed through the form. I need to get those 2 dates, find every day in between and insert a record for each day with a schedule on it. I do not have the insert statement done at this time. I am merely trying to get and display the proper date range.
            • 3. Re: Problem with between date ranges
              Dan Bracuk Level 5
              something like this.
              ThisDate = ParseDateTime(form.firstdate);
              FinalDate = ParseDateTime(form.firstdate);

              <cfloop condition = "ThisDate lte FinalDate:>
              <cfquery>
              insert into yourtable
              (fields)
              values
              (values inlcuding ThisDate variable)
              </cfquery>
              <cfset ThisDate = DateAdd("d", 1, ThisDate)
              </cfloop>
              • 4. Re: Problem with between date ranges
                siriiven Level 1
                Thanks I'll give this a try.
                • 5. Re: Problem with between date ranges
                  cf_dev2 Level 1
                  > WHERE ((DateAdd('d', DaysSpan.Counter, '#form.firstday#')) <= '#form.lastday#')

                  Do you mean something like this..?

                  <!--- assumes start/end are valid datetime objects --->
                  SELECT DateAdd('d', DaysSpan.Counter, #yourStartDateTime#)
                  FROM DaysSpan
                  WHERE DaysSpan.Counter <= #dateDiff("d", yourStartDateTime, yourEndDateTime)#

                  • 6. Re: Problem with between date ranges
                    siriiven Level 1
                    Yes that is what I am thinking but it has to be something like:


                    SELECT DateAdd('d', DaysSpan.Counter, #yourStartDateTime#)
                    FROM DaysSpan
                    WHERE DateAdd('d', DaysSpan.Counter, #yourStartDateTime#) <= #dateDiff("d", yourStartDateTime, yourEndDateTime)#

                    DaysSpan.Counter is just a number between 0-366. So that whatever the firstday is it adds the counter to it all the way up to 366 to generate the dates. I want it to stop though when it hits the lastday.



                    • 7. Re: Problem with between date ranges
                      cf_dev2 Level 1
                      > Yes that is what I am thinking but it has to be something like:

                      Why?

                      Did you try it?
                      • 8. Problem with between date ranges
                        siriiven Level 1
                        Yes I tried it. I did not get any errors but it did not work as I need it to. Say #firstday# is 01/15/2008. I need to add my dayspan.counter to it to increment the days by one. There are 0-366 numbers it adds to it. So I would get 01/16/2008, 01/17/2008, etc. Then I want to stop it when it reaches like 05/12/2008. When I ran your query I got results that were like 12/31/1899, 01/01/1900.

                        I need to compare the date values I think, not just the difference between them.
                        • 9. Re: Problem with between date ranges
                          cf_dev2 Level 1
                          > <!--- assumes start/end are valid datetime objects --->
                          > When I ran your query I got results that were like 12/31/1899, 01/01/1900.

                          I suspect you did not parse the form values into datetime objects first, and that's why you're seeing those results. If you just pass in a string like 01/15/2008, the database will treat the value as a numeric date (1 divided by 15 divided by 2008). Resulting in dates like 01/01/1900. It works correctly if you pass in a datetime object.

                          Take a look at the date functions: CreateDateTime, ParseDateTime, etc... or <cfqueryparam> and its date/time cfsqltypes.