Running a query on records in a month

ndeeley

Hi,

I'm trying to extract records for this month and output the count as a figure. I also want to do this for last month. Obviously this will be dynamic so the months update themselves with the system clock.

I have written the code below but it doesn't seem to pull the correct records from my db - I only have records in there for August (Short Date dd/mm/yyyy) but it shows the same figure for Sept & Aug.

Can anyone help?

Expand|Select|Wrap|Line Numbers

 
<cftransaction>

<cfset ThisMonth = Month(Now())>

<cfset LastMonth = Month(Now())-1>

<cfset ThisYear = Year(Now())>

<cfset DIM = DaysInMonth(Now())>

<cfset TMDate = '01/#ThisMonth#/#ThisYear#'>

<cfset LMDate = '#DIM#/#LastMonth#/#ThisYear#'>

</cftransaction>
 
<!--- Start of Colfusion block. Builds list data --->

<cftransaction>
 
    <cfquery datasource="wflow" name="GetWIRecTM">

    select        *

    from        tblRecords    

    where        DateRecordAdded Between #CreateODBCDate("#TMDate#")# AND #createODBCDate("#LMDate#")#                    
 
    </cfquery>
 
    <cfquery datasource="wflow" name="GetWIRecLM">

    select        *

    from        tblRecords    

    where        DateRecordAdded BETWEEN #CreateODBCDate("01/#LastMonth#/#ThisYear#")# AND #CreateODBCDate("#DIM#/#LastMonth#/#ThisYear#")#

Thanks
Neil

acoder

Expand|Select|Wrap|Line Numbers

 DateRecordAdded Between #CreateODBCDate("#TMDate#")# AND #createODBCDate("#LMDate#")#
 

looks incorrect. It should be between the first and last of this month?

ndeeley

Hey! Welcome back!

Yes, that was wrong - I've corrected it and it still isn't working. Do you think it might be because the original DateRecordAdded isn't going in as a CreateODBC date?

ndeeley

Ha! I have had alook and ThisMonths total is also counting last months - any idea why? It's stumping me!

ndeeley

It's strange. Going back to basic code this query works:

Expand|Select|Wrap|Line Numbers

 
DateRecordAdded BETWEEN #createODBCDate('01-Sep-09')# AND #createODBCDate('30-Sep-09')#

But this doesn't:

DateRecordAdded BETWEEN #createODBCDate('01-#ThisMonth#-09')# AND #createODBCDate('30-#ThisMonth#-09')#

where ThisMonth is

Expand|Select|Wrap|Line Numbers

<cfset ThisMonth = Month(Now())

and evaluates to 9.

ndeeley

Hmm.

Managed to get it to work by doing this:

Expand|Select|Wrap|Line Numbers

 
<cfset ThisMonth = Month(Now())>

<cfset TMS = MonthAsString(ThisMonth)>

<cfset LastMonth = Month(Now())-1>

<cfset LMS = MonthAsString(LastMonth)>

<cfset ThisYear = Year(Now())>

<cfset DIM = DaysInMonth(Now())>
 
<cfquery name="GetThisMonth" datasource="wflow">
 
    select         ID, 

            SchemeType,

            AddedByFK,

            SchemeCode,

            SchemeTitle,

            DateRecordAdded

    from        tblRecords

    where        DateRecordAdded BETWEEN #createODBCDate('01-#TMS#-#ThisYear#')# AND #createODBCDate('#DIM#-#TMS#-#ThisYear#')#

    order by    DateRecordAdded
 
</cfquery>

But it feels that I`ve bodged it somehow!

acoder

It'll work with the month as a string because then it's the correct date whereas with numbers, the month will be first. To avoid these problems with numbers, you could use the format yyyy-mm-dd.

Running a query on records in a month

re: Running a query on records in a month

re: Running a query on records in a month

re: Running a query on records in a month

re: Running a query on records in a month

re: Running a query on records in a month

re: Running a query on records in a month

Similar ColdFusion bytes

/bytes/development

/bytes/it

/bytes/about