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

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.

type is date only, I have below rule to get a count between a particular month, I want it to be dynamic so every month data it will be updated. in DB data will be of last month, so in December, DB will have data of November month.Currently value is hard coded , i want it to be dynamic.

a!queryEntity(
entity: cons!ds_cons,
query: a!query(
logicalExpression: a!queryLogicalExpression(
operator: "AND",
filters:{
a!queryFilter(
field: "date",
operator: "<=",
value: todate("30-NOV-2019")
),
a!queryFilter(
field: "date",
operator: ">=",
value: todate("01-NOV-2019")
)
}
),
pagingInfo: a!pagingInfo(
1,
- 1
)
)
).totalcount

So you mean you want it to do like the above filtering, but always automatically show the data for the month right before the current month?  IE on January 1st, it would automatically switch to filtering between Dec 1st and 31st, etc?

yes , correct.

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.

Thanks Mike.

There is the eomonth() function too

a!localVariables(
  end: eomonth(today(), -1),
  start: date(year(local!end), month(local!end), 1),
  "Between " & local!start & " and " & local!end
)

good point - i think i wrote the above rules either before eomonth() existed, or at least before I knew about it ;-)

good point - i think i wrote the above rules either before eomonth() existed, or at least before I knew about it ;-)