Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I have did that by using PurgeChar , Subfield and the the Date#
Thanks alot guys ...........
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
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,
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
I have did that by using PurgeChar , Subfield and the the Date#
Thanks alot guys ...........