2 Replies Latest reply: Feb 14, 2013 8:01 AM by sliderrules RSS

    Date Format

    sliderrules
      Hi,

      I am filtering on a report using the date:

      date between TimeStampAdd(SQL_TSI_MONTH, -12, TIMESTAMPADD(SQL_TSI_DAY,DAYOFMONTH(CURRENT_DATE) * -1 , CURRENT_DATE)) and TIMESTAMPADD(SQL_TSI_DAY,DAYOFMONTH(CURRENT_DATE) * -1 , CURRENT_DATE)


      I get the following message:

      The value entered must be a date and time in the form 30/11/2003 23:50:58

      I have changed the column date format to custom DD/MM/YYYY but still no luck.

      Can anyone help with a workaround?

      Thanks
        • 1. Re: Date Format
          Srini VEERAVALLI
          You need to change the format of date column using cast. use cast(datecol as date) and then rest of condition.

          cast (dateColumn as date) between TimeStampAdd(SQL_TSI_MONTH, -12, TIMESTAMPADD(SQL_TSI_DAY,DAYOFMONTH(CURRENT_DATE) * -1 , CURRENT_DATE)) and TIMESTAMPADD(SQL_TSI_DAY,DAYOFMONTH(CURRENT_DATE) * -1 , CURRENT_DATE)

          Let me know how it works

          Edited by: Srini VEERAVALLI on Feb 14, 2013 7:43 AM

          Or else the below with no cast

          date between TimeStampAdd(SQL_TSI_MONTH, -12, TIMESTAMPADD(SQL_TSI_DAY,DAYOFMONTH(CURRENT_TIMESTAMP(1)) * -1 , CURRENT_TIMESTAMP(1))) and TIMESTAMPADD(SQL_TSI_DAY,DAYOFMONTH(CURRENT_TIMESTAMP(1)) * -1 , CURRENT_TIMESTAMP(1))

          Edited by: Srini VEERAVALLI on Feb 14, 2013 7:47 AM
          • 2. Re: Date Format
            sliderrules
            Thanks that worked with cast (dateColumn as date)