Date string/month comparison

http://www.dbforums.com/archive/index.php/t-903211.html

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)

agx,

I keep receiving a "missing right parenthesis" error. I've checked through andf typed in exactly what you have.

agx,

Apologies i wasn't clear from the start.

I'm actually using Actuate E Reporting to make a report.

Not sure which version.

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) & "'"

       'Build the SQL statement the clauses
       
        ObtainSelectStatement = Super::ObtainSelectStatement( )

        '  Show the statement in the job summary if desired
           '  ShowFactoryStatus(ObtainSelectStatement)

     End Function

Uncomment the ShowFactoryStatus To debug the SQL

Typo in function:

     '-------------------------------------------------------------------------
     ' TfStampFromDate(Date)   - Convert a Date into string in the form yyyymmdd
     '-------------------------------------------------------------------------
     Function TfStampFromDate(
     +   InputDate As Date
     +   ) As String
         TfStampFromDate =  Format( InputDate, "yyyymmdd" )
     End Function