Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi community,
I have date column in my csv file in two formats,
1.format = mm/dd/yyyy
2.number format= 41730,
monthname(Date(alt("Inv Date", Date(Date#("Inv Date", 'MM/DD/YYYY'), 'MMM-YYYY'),
Date(num("Inv Date"), 'MMM-YYYY') ))) as "Invoice Date" ,
used above script to change the format,
when i am trying to change the format to mmm-yyyy, system is getting confused to take some records
for example : 10/01/2015(oct 1st 2015) but it is loading as Jan 2015(Jan 10th 2015).
And am not sure how the number format is changing.
Please help me how can I resolve this.
This should work for you:
Date(Date#([Inv date], 'MM/DD/YYYY'), 'MMM-YYYY') as [Expected Output]
May be this:
MonthName(Alt(Date#("Inv Date", 'MM/DD/YYYY'), "Inv Date")) as "Invoice Date" ,
Try this:
=date(NumberFormatField,'MMM-YYYY') as NumberFormatField
or even this:
SET DateFormat='MM/DD/YYYY';
MonthName(Date("Inv Date")) as "Invoice Date"
Hi sunny,
thanks for the reply,
first 10 days of oct are taking as different months,
10/01/2015 as 01/10/2015 (i need Oct 1st)
.
.
.
10/12/2015 as 10/12/2015( I need Oct 12th).
thank you
Try this :
date(Addmonths(Date#(Date,'Format'),0),'Format')
Hi Balraj,
Not working,
I have two formats in same column.
10/01/2015
41750
I need to change both to MMM-YYYY format
Thnak you
Hi Punit,
this is the output am getting when I used the above expression
Care to post some sample csv data?
Hi,
sample data:
Inv date:(MM/dd/yyyy) Output am getting Expected output
10/01/2015 Jan-2015 Oct-2015
10/02/2015 Feb-2015 Oct-2015
10/03/2015 Mar-2015 Oct-2015
10/04/2015 Apr-2015 Oct-2015
10/05/2015 May-2015 Oct-2015
10/06/2015 Jun-2015 Oct-2015
10/07/2015 Jul-2015 Oct-2015
10/08/2015 Aug-2015 Oct-2015
10/09/2015 Sep-2015 Oct-2015
10/10/2015 Oct-2015 Oct-2015
10/11/2015 Nov-2015 Oct-2015
10/12/2015 Dec-2015 Oct-2015
from 10/13/2015 are storing in Oct month.
thank you