5 Replies Latest reply on Aug 24, 2009 7:55 AM by Smdsports.com

    Date problem

    Smdsports.com

      I have never been very good at dates

       

      I have a calendar that is not woking properly since I switched the db from access to sql  Learning SQL also

       

      <cfif isdefined("month")>
          <cfparam name="startdate" default=#createdate(year,month,1)#>
      </cfif>
      <cfparam name="startdate" default='#month(now())#/1/#year(now())#'>
      <cfset #enddate# = #dateadd('m',1,startdate)#>
      <cfset #enddate# = #dateadd('d',-1,enddate)#>

       

       

      <cfquery name="GetEvents" datasource=#dbname#>
        Select * from tblCalendar
        Where  (Evt_Date >= ###dateformat(startdate,'mm/dd/yyyy')### AND Evt_Date <= ###datedateformat(enddate,'mm/dd/yyyy')###)
          AND evt_approved = 1 and siteid='#siteid#' and scoutid=<cfqueryparam value="#url.scoutid#" cfsqltype="cf_sql_varchar">
        </cfquery>

       

      The problem is this..  It displays the current month just fine, however, when I click next or previous month, it does not change...stays on august.

       

      Not sure what I am missing.

       

      Thanks in Advance

      Sharon

      www.smdscouts.com

        • 1. Re: Date problem
          Dan Bracuk Level 5

          You are not using createdate the way the cfml reference manual describes it.

           

          In your query, don't use dateformat.  It returns a string, not a date object.

          • 2. Re: Date problem
            BKBK Adobe Community Professional & MVP

            This might or might not help, but I think it reads easier

             

            <cfif isdefined("variables.month")>
                <cfset startdate = createdate(variables.year,variables.month,1)>
            <cfelse>
                <cfset monthNow = month(now())>
                <cfset yearNow = year(now())>
                <cfset startdate = createdate(yearNow, monthNow, 1)>
            </cfif>

             

            <cfset enddate = dateadd('m',1,startdate)>
            <cfset enddate = dateadd('d',-1,enddate)>

             

            <cfquery name="GetEvents" datasource=#dbname#>
                  Select *
                from tblCalendar
                Where  Evt_Date >= #createODBCDate(startdate)#
                AND Evt_Date <= #createODBCDate(enddate)#
                AND evt_approved = 1
                and siteid='#siteid#'
                and scoutid=<cfqueryparam value="#url.scoutid#" cfsqltype="cf_sql_varchar">
              </cfquery>

            • 3. Re: Date problem
              Smdsports.com Level 1

              Thank you, It did clean things up a bit but did not fix the problem.

               

              On the calendar, when someone clicks next, it passes the next month in the url ., ie month number 9

               

              problem is, query is not pulling it, its stil pulling the month of 8 and as I previously stated dates and me do not mix.  hurts my head just looking at it.

               

               

              <cfif isdefined("variables.month")>
                  <cfset startdate = createdate(variables.year,variables.month,1)>
              <cfelse>
                  <cfset monthNow = month(now())>
                  <cfset yearNow = year(now())>
                  <cfset startdate = createdate(yearNow, monthNow, 1)>
              </cfif>

               

              <cfset enddate = dateadd('m',1,startdate)>
              <cfset enddate = dateadd('d',-1,enddate)>

               

              <cfquery name="GetEvents" datasource="smdscout">
                    Select *
                  from tblCalendar
                  Where  Evt_Date >= #createODBCDate(startdate)#
                  AND Evt_Date <= #createODBCDate(enddate)#   
                  and scoutid=<cfqueryparam value="#url.scoutid#" cfsqltype="cf_sql_varchar">
                </cfquery>

              • 4. Re: Date problem
                Dan Bracuk Level 5

                You are using isDefined on the variables scope instead of the url scope.  Unless you have some code that creates variables.month, the function will always return false.

                • 5. Re: Date problem
                  Smdsports.com Level 1

                  Well, I did a short/long cut to fix the problem...but thank you dan for the hint.  the variable for month was not be set.