4 Replies Latest reply: Feb 4, 2014 1:58 AM by Shashank Seth RSS

    To extract date from String

    Shashank Seth

      Hi Geniuses,

       

      I Have a Contractual year in string form

       

      1 Apr 2012 to 31 Mar 2014
      1 Dec 2013 to 30 Nov 2014
      1 Jan 2011 to 31 Dec 2011
      1 Jan 2011 to 31 Dec 2015
      1 Jan 2012 to 31 Dec 2012
      1 Jan 2012 to 31 Dec 2013
      1 Jan 2013 to 31 Dec 2013
      1 Jan 2013 to 31 Dec 2014
      1 Jan 2014 to 31 Dec 2014
      1 Jan 2015 to 31 Dec 2015
      1 Jul 2012 to 30 Jun 2014
      1 Jul 2013 to 30 Jun 2014
      1 Nov 2013 to 31 Oct 2014
      1 Nov 2013 to 31 Oct 2015
      1 Oct 2013 to 30 Sep 2014
      1 Sep 2012 to 31 Aug 2014
      15 Apr 2013 to 14 Apr 2014

       

      Now I want the start and the end date from these Contractual year

       

      so I want something like this

       

      Contractual Year Start

       

      1/01/2011

      1/01/2012

      1/04/2012

      1/07/2012

      1/09/2012

      1/01/2013

      15/04/2013

      1/07/2013

      1/10/2013

      1/11/2013

      1/12/2013

      1/01/2014

      1/01/2015

       

      Contractual Year end

       

      31/12/2011

      31/12/2012

      31/12/2013

      31/03/2014

      14/04/2014

      30/06/2014

      31/08/2014

      30/09/2014

      31/10/2014

      30/11/2014

      31/12/2014

      31/10/2015

      31/12/2015

       

      So now you see i f I select Contractual year as

       

      1 Apr 2012 to 31 Mar 2014 my Contractual start will be 01/01/2012 and Contractual end will be 31/03/2014

       

      Thanks in advance

       

      S

        • Re: To extract date from String
          Celambarasan Adhimulam

          Derive the fields from script itself like

          Date(Date#(SubField(ContractualYear, ' to ', 1), 'D MMM YYYY'), 'DD/MM/YYYY') AS ContractualStart

          Date(Date#(SubField(ContractualYear, ' to ', 2), 'D MMM YYYY'), 'DD/MM/YYYY') AS ContractualEnd

           

          It will create two fields as expected

           

          Thanks

          Celambarasan

            • Re: To extract date from String
              Suresh Baabu

              Please convert your string to date using the expressions below on your load script

               

              Please go with SubField function as suggested above. But, you can make use of Left and right functions if the source date format is different.

               

              date(Date#(left(datecolumn,11),'DD MMM YYYY'),'DD/MM/YYYY') as start_date,

              date(Date#(Right(datecolumn,11),'DD MMM YYYY'),'DD/MM/YYYY') as End_date

               

               

              Hope it helps!!

              Thanks,

            • Re: To extract date from String
              Vishwaranjan Kumar

              hi

              for start date--

              =Date(Date#(left('1 Apr 2012 to 31 Mar 2014',11),'DD MMM YYYY'),'DD/MM/YYYY')

              for end date--

              =Date(Date#(Right('1 Apr 2012 to 31 Mar 2014',11),'DD MMM YYYY'),'DD/MM/YYYY')

               

              ********************************************************************************************************

              in script write this

              Date(Date#(left(Contractualdate,11),'DD MMM YYYY'),'DD/MM/YYYY') as startdate

              Date(Date#(Right(Contractualdate,11),'DD MMM YYYY'),'DD/MM/YYYY')  as enddate