String to month name

[#1]

Hi all,

I want to convert a date to month number + year (e.g., jan-2014), but I cannot get it to work. My dates are in the following format: 20140130. My script looks like this:

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;€ -#.##0,00';

SET TimeFormat='h:mm:ss';

SET DateFormat='D-M-YYYY';

SET TimestampFormat='D-M-YYYY h:mm:ss[.fff]';

SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec';

SET DayNames='ma;di;wo;do;vr;za;zo';

LOAD [Transaction No.],

     Custodian,

     Date(Date#([Trade date],'YYYYMMDD'),'D-M-YYYY') as [Trade date],

     month([Trade date]) as Month,

.

.

.

.

.

My intention here was to convert the date to the format D-M-YYYY and then to read the month from the new format, but when I try it like this I get other months displayed. Can anyone provide a solution for this? Thanks!

[Michiel_QV_Fan · Accepted solution]

You are almost there:

month(Date#([Trade date],'YYYYMMDD')&'-'&year(Date#([Trade date],'YYYYMMDD') as [Trade date], (jan-2014)

num(month(Date#([Trade date],'YYYYMMDD'), '00')&'-'&year(Date#([Trade date],'YYYYMMDD') as [Trade date], (01-2014)

num(month(Date#([Trade date],'YYYYMMDD'), '0')&'-'&year(Date#([Trade date],'YYYYMMDD') as [Trade date], (1-2014)

[#3]

Hi,

it works! Thanks! Is it also possible to link this MonthYear with the underlying date? I want to keep my dates and want to perform a drill down in a graph so that when clicking on a bar with MonthYear, I get to see the underlying dates. My script now looks like this:

LOAD [Transaction No.],

       Date(Date#([Trade date],'YYYYMMDD'),'D-M-YYYY') as [Trade date],

  month(Date#([Trade date],'YYYYMMDD'))&'-'&year(Date#([Trade date],'YYYYMMDD')) as [MonthYear],

[Michiel_QV_Fan]

Not exactly what you need?