How to get a count of data from database between a date range

Question

Hello , 
 I have a column of dates in the table as "09-DEC-19" , i have a requirement to show a count of data between first and last day of month. Say i want to get the count of data from Nov 1st to Nov 30th NOV. 
 
 Same should populate every month. Please assist.

Mike Schmitt · Answer

Is there any particular reason you're not storing dates as a DATE or DATETIME column in the table? That way you would be able to use simple filtering in your query. If possible, I'd suggest either changing the existing column to use the DATE type, or add a secondary column to act as basically a duplicate of the original column but storing the DATE format instead of the text you're currently using.

Mike Schmitt · Answer

lucky coincidence - i previously wrote expression rules to return the start and end dates for the previous month, adjusting for things like year boundary, leap year, etc. For full robustness you would use the following 3: 
 /* GLBL_returnEndDateForMonth */

if(
 or(
 isnull(ri!year),
 isnull(ri!month),
 ri!month < 1,
 ri!month > 12
 ),
 null(),

 a!localVariables(
 
 local!monthlyDayTotals: {
 /* January */ 31,
 /* February */ 28 + if(isleapyear(ri!year),1,0),
 /* March */ 31,
 /* April */ 30,
 /* May */ 31,
 /* June */ 30,
 /* July */ 31,
 /* August */ 31,
 /* September */ 30,
 /* October */ 31,
 /* November */ 30,
 /* December */ 31
 },
 
 local!monthEndDay: index(
 local!monthlyDayTotals,
 ri!month
 ),
 
 local!monthEndDate: date(
 ri!year,
 ri!month,
 local!monthEndDay
 ),
 
 local!monthEndDate
 )
) 
 /* GLBL_returnPreviousMonthStartDate */

a!localVariables(
 
 local!currentYear: year(ri!date),
 local!previousYear: local!currentYear - 1,
 local!currentMonth: month(ri!date),
 
 local!previousMonth: index(
 {12, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11},
 local!currentMonth
 ),
 
 local!previousMonthYear: if(
 local!currentMonth = 1,
 local!previousYear,
 local!currentYear
 ),
 
 local!previousMonthStartDate: date(
 local!previousMonthYear,
 local!previousMonth,
 1
 ),
 
 local!previousMonthStartDate
) 
 /* GLBL_returnPreviousMonthEndDate */

a!localVariables(
 
 local!currentYear: year(ri!date),
 local!previousYear: local!currentYear - 1,
 local!currentMonth: month(ri!date),
 
 local!previousMonth: index(
 {12, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11},
 local!currentMonth
 ),
 
 local!previousMonthYear: if(
 local!currentMonth = 1,
 local!previousYear,
 local!currentYear
 ),
 
 local!previousMonthEndDate: rule!GLBL_returnEndDateForMonth(
 year: local!previousMonthYear,
 month: local!previousMonth
 ),
 
 local!previousMonthEndDate
) 
 After you have these 3 rules installed, you would simply need to change your filters to call the "return previous month start date" and "return previous month end date" rules.