Changing Date Format

[#1]

Hello,

I would like some help with date formatting.

I am using this master calendar:

Temp:

Load num#([Date],'#') as NumericDate

RESIDENT [Dates];

MinMax:

LOAD Min(NumericDate) AS MinDate,

   Max(NumericDate) AS MaxDate

RESIDENT Temp;

LET vMinDate = Num(Peek('MinDate',0,'MinMax'));

LET vMaxDate = Num(Peek('MaxDate',0,'MinMax'));

MasterCalendar:

LOAD

text(num(MonthStart(Date($(vMinDate) + RecNo() - 1)))) AS [Date],

Year(Date($(vMinDate) + RecNo() - 1)) as [Year],

Month(Date($(vMinDate) + RecNo() - 1)) as [Month],

    ceil(month(Date($(vMinDate) + RecNo() - 1))/3) as Quarter,

Date(monthStart(Date($(vMinDate) + RecNo() - 1)), 'MMM-YYYY') AS [MonthYear]

AUTOGENERATE ($(vMaxDate) - $(vMinDate)+1);

DROP TABLE MinMax;

DROP TABLE Temp;

But my dates appear in the 40295, 42102 format.

Is there any changes I can make to my master calendar in order to ensure that it is in the DD/MM/YYYY format?

Best wishes,

Alison

[petter · Accepted solution]

Absolutely Alison,

what you need to do is make sure that after you do calculations with a date is that you wrap it in a Date() function to convert it back to date from the numerical basic date that you get after a calculation.

So for example in your script instead of doing:

    text(num(MonthStart(Date($(vMinDate) + RecNo() - 1)))) AS [Date],     

you should do something like this:

    Date(MonthStart($(vMinDate) + RecNo() - 1), 'DD/MM/YYYY) AS [Date]

[#3]

Hi Petter,

Thank you for your response.

My dates have now become formatted in a variety of ways as you can see here:

Do you think that the issue lies in the raw data?

[#4]

I think the error does lie with the raw data. Thank you for your help.

[petter]

If you get various date formats in your raw data you can use the Date# interpretation function to convert them into real dates. Then you can get them all to be shown in a uniform date format.