This discussion is archived
2 Replies Latest reply: Feb 14, 2013 6:01 AM by sliderrules RSS

Date Format

sliderrules Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks that worked with cast (dateColumn as date)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points