Link to home
Create AccountLog in
Avatar of Ritesh_Mistry
Ritesh_Mistry

asked on

Date string/month comparison

Dear experts,

I have a field with a date in but it is in a string format as follows: yyyymmdd e.g. 20070531.

I wish to do a comparison in my query so that the results from my query would equal the current months date.

how do i do this using the date filed above?

Thanks
Avatar of Chandan_Gowda
Chandan_Gowda
Flag of United States of America image

Avatar of _agx_
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)
Avatar of Ritesh_Mistry
Ritesh_Mistry

ASKER

agx,

I keep receiving a "missing right parenthesis" error. I've checked through andf typed in exactly what you have.
ASKER CERTIFIED SOLUTION
Avatar of _agx_
_agx_
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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