You would need to convert the string column to a date
--- get records for todays date SELECT * FROM YourTable WHERE str_to_date(YourStringColumn, '%Y%m%d') = current_date()
--- get records between first of this month and last day of this month WHERE str_to_date(YourStringColumn, '%Y%m%d') >= date_add(current_date(), INTERVAL -day(current_date())+1 DAY) AND str_to_date(YourStringColumn, '%Y%m%d') < date_add(current_date()-day(current_date())+1, INTERVAL 1 MONTH)
I'm not familiar with Actuate E Reporting, but the basic query logic still applies.
Though on second thought it sounds like maybe your table contains a DATETIME column and you're just trying to pass in a string date parameter. In which case you could pass in the first and end of the month like this
SELECT * FROM YourTable WHERE YourDateColumn >= '2007-05-01' AND YourDateColumn <= '2007-05-30'
... or if your column contains date and time
SELECT * FROM YourTable WHERE YourDateColumn >= '2007-05-01' AND YourDateColumn < '2007-06-01' -- less than first of NEXT month
Build some basic Time/Date functions into a library to include in your reports
' FILE NAME : TimeFunctions.bas ' ' DESCRIPTION : This file implements some basic functions for Time/date for Actuate ' ' '------------------------------------------------------------------------- ' TfDatefromStamp(String) - Convert a String in the form 'yyyymmdd' into a date '------------------------------------------------------------------------- Function TfDatefromStamp( + InputString As String + ) As Date TfDatefromStamp = CDate(Str$(Mid$(InputString,5, 2) & "/" & Mid$(InputString, 7, 2) & "/" & Mid$(InputString, 1, 4))) End Function ' '------------------------------------------------------------------------- ' TfStampFromDate(Date) - Convert a Date into string in the form yyyymmdd '------------------------------------------------------------------------- Function TfStampFromDate( + InputDate As Date + ) As String TfShorTftamp = Format( InputDate, "yyyymmdd" ) End Function ' Function TfMonthEndDate( + InputDate As Date + ) As Date TfMonthEndDate = InputDate 'assign Date to input date TfMonthEndDate = CDate(str$(Month(TfMonthEndDate)) & "/1/" & str$(Year(TfMonthEndDate))) ' set to first of month TfMonthEndDate = DateAdd("m",1,TfMonthEndDate) ' set to first of next month TfMonthEndDate = DateAdd("d",-1,TfMonthEndDate) ' set to end of this month End Function ' Function TfMonthStartDate( + InputDate As Date + ) As Date TfMonthStartDate = InputDate 'assign Date to input date TfMonthStartDate = CDate(str$(Month(TfMonthStartDate)) & "/1/" & str$(Year(TfMonthStartDate))) ' set to first of month ' End Function ' Function TfMonthEndStamp( + InputDate As Date + ) As String TfMonthEndstamp = TfStampFromDate(TfMonthEndDate(InputDate)) End Function ' Function TfMonthStartStamp( + InputDate As Date + ) As String TfMonthStartStamp = TfStampFromDate(TfMonthStartDate(InputDate)) End Function
You Can Then Override(Replace) the WhereClause In ObtainSelectStatement Function of SQLQuerySource
Function ObtainSelectStatement( ) As String
'Replace the where clause WhereClause = " YourStringColumn >= '" & TfMonthStartStamp(Date) & "' AND YourStringColumn <= '" & TfMonthEndstamp(Date) & "'"
'------------------------------------------------------------------------- ' TfStampFromDate(Date) - Convert a Date into string in the form yyyymmdd '------------------------------------------------------------------------- Function TfStampFromDate( + InputDate As Date + ) As String TfStampFromDate = Format( InputDate, "yyyymmdd" ) End Function
