1 Reply Latest reply: May 9, 2013 11:31 AM by minimuke RSS

    Date syntax in if statement

    Rory McHugh

      Hi

       

      I am trying to write a simple if statement to group my dates by financial year. (I am dealing with just 14 dates, so happy to hard code this, rather than use a more complicated script to get a financial year for any date.)

       

      This is the script:

       

      MonthlyReportDatesTemp:

      Load

                Distinct UploadEngagementDate as UploadEngagementDate

      Resident UploadEngagementCLRN;

       

      MonthlyReportDates:

      Load

                UploadEngagementDate,

                Month(UploadEngagementDate) as UploadEngagementMonth,

                if(((UploadEngagementDate >= 01/04/2012) and (UploadEngagementDate < 01/04/2013)),'1213','1314') as UploadEngagementFY

      Resident MonthlyReportDatesTemp

      Order By UploadEngagementDate asc;

      DROP Table MonthlyReportDatesTemp;

       

      This is the table that the script is pointed at.

       

      LeadCLRN UploadEngagementDate UploadEngagement%CLRN
      BBC 16/04/2012 0.92895586652314
      BBC 21/05/2012 0.99891540130152
      BBC 18/06/2012 0.98692810457516
      BBC 16/07/2012 0.97986577181208
      BBC 20/08/2012 0.99325084364454
      BBC 17/09/2012 0.9875
      BBC 15/10/2012 0.98470588235294
      BBC 19/11/2012 0.97690531177829
      BBC 17/12/2012 0.9749430523918
      BBC 21/01/2013 0.97477064220183
      BBC 18/02/2013 0.97119815668203
      BBC 18/03/2013 0.96579247434436
      BBC 15/04/2013 0.95537757437071
      BBC 07/05/2013 0.97368421052632
      CDTV 16/04/2012 0.98888888888889
      CDTV 21/05/2012 1
      CDTV 18/06/2012 0.98958333333333
      CDTV 16/07/2012 1
      CDTV 20/08/2012 0.99029126213592


      And this is the resulting table

      UploadEngagementDate UploadEngagementFY UploadEngagementMonth
      16/04/2012 1314 Apr
      21/05/2012 1314 May
      18/06/2012 1314 Jun
      16/07/2012 1314 Jul
      20/08/2012 1314 Aug
      17/09/2012 1314 Sep
      15/10/2012 1314 Oct
      19/11/2012 1314 Nov
      17/12/2012 1314 Dec
      21/01/2013 1314 Jan
      18/02/2013 1314 Feb
      18/03/2013 1314 Mar
      15/04/2013 1314 Apr
      07/05/2013 1314 May

       

      Am I using the wrong syntax for dates here or am I doing something else wrong? I have tried putting qualifiers round the date and using the real date format (e.g. 45786) but still not getting it right.

       

      Any advice much appreciated.

      Thanks
      Rory