Date Format

[danielnevitt]

Hi,

I have a field that is named period_cd and is text in the format MMM-YY (JAN-18).  Is there a way that I can easily convert this to format YYYYMM (201801)?

Thanks,

Daniel

[Chanty4u · Accepted solution]

try

date(Date#(Datefield,'MMM-YY'),'YYYYMM') as NewDate

[Anonymous]

Hi ,

Use the below syntax

Date(Date#(Datefield,'MMM-YY') , 'YYYYMM')

Thanks & regards,

Venkata Sreekanth

[danielnevitt]

Thank you both for the replies.

Unfortunately the above code does not work.

APR-18 returns 201800 (rather than 201804).  Is this because the period_cd field is text rather than a date?

Regards,

Daniel

[Chanty4u]

cn you share sample data?

[YoussefBelloum]

what do you have on your Monthnames variables ?

do you have this: Set MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'; ??

[danielnevitt]

Hi,

The variables I have set are:

SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET NullDisplay='-';

Regards,

Daniel

[YoussefBelloum]

"Is this because the period_cd field is text rather than a date?"

Even if this field is a text, using Date#() the text will be evaluated as a Date.

[danielnevitt]

Hi,

The code you posted, but I think got deleted from the above communication works:

=Date(Date#(right(period_cd,2)&num(month(date#(left(period_cd,3),'MMM'))),'YYMM')),'YYYYMM')

Regards,

Daniel

[danielnevitt]

I also have two other fields:

Mat_Year = format yyyy

Mat_Month = format mm

Is it possible to join these fields together to create format mmm-yy?

Regards,

Daniel