Date format

[telly]

Hi , 

I need help with formatting the Date, as of the moment the format that I have is 01/01/2020 and I want it to be display like this January - 01 - 2020.

Is this possible? Thanks.

[SunakoChan]

Hi @Telly,

I suppose that you are using Date/Time field. Since it is a Data/time field you have to convert the Field in to text after that use substring for the month, you may use this formula below:

CONVERT(VARCHAR, (SUBSTRING('JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC ', ((Datepart(month, [@field: DATEFIELD])) * 4) - 3, 3))) + ' -' + CONVERT(VARCHAR, (Datepart(day, [@field: DATEFIELD))) + ' - ' + CONVERT(VARCHAR, (Datepart(year, [@field: DATEFIELD])))

I hope this helps.

[vanderLeest]

Thanks SunakoChan.

to create a rendering of a period between a Start and and End date like Jan '19 - Feb '20,

Dmytro and I came up with the following formula (for a formula field), using your substring idea:

CONVERT(VARCHAR, (SUBSTRING('Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec ', ((Datepart(month, [@field:MS_StartDt])) * 4) - 3, 3))) + ' ' + Char(39) + CONVERT(VARCHAR, (Right(Year([@field:MS_StartDt])+ 1,2)))  + ' - ' + CONVERT(VARCHAR, (SUBSTRING('Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec ', ((Datepart(month, [@field:MS_EndDt])) * 4) - 3, 3))) + ' ' + Char(39) + CONVERT(VARCHAR, (Right(Year([@field:MS_EndDt])+ 1,2)))