Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

String to month name

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!

1 Solution

Accepted Solutions
Michiel_QV_Fan
Specialist
Specialist

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)

View solution in original post

3 Replies
Michiel_QV_Fan
Specialist
Specialist

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)

Not applicable
Author

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
Specialist
Specialist

Not exactly what you need?