Skip to main content
A
Anonymous
August 10, 2009
Question

Date problem

  • August 10, 2009
  • 3 replies
  • 1213 views

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

This topic has been closed for replies.

3 replies

A
Anonymous
August 24, 2009

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.

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
August 11, 2009

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>

A
Anonymous
August 24, 2009

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>

D
Dan_Bracuk
Inspiring
August 24, 2009

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.

D
Dan_Bracuk
Inspiring
August 10, 2009

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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices