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

 

[Kurumi]

Hi - I would like to add on this post. In case, you would like to get the month using DATEPART() and add leading zero, you may use these formulas:

RIGHT('0' + CAST(DATEPART(month, [@field:DATE]) AS VARCHAR(10)), 2)

or 

RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(MM, [@field:DATE])), 2)

Result:

January = 1 to 01
February = 2 to 02

Since the DATEPART() function returns an integer, leading zero's will always disappear and need to be manually added like so.

Reference:
https://www.w3schools.com/sql/func_sqlserver_datepart.asp

[MickyMartian232]

Date/Time Formats

Last Updated: 2021-03-03

The Date Formats global option changes the default date format for all maps or forms. However, the format of the existing date fields do not change; the default is only used for new maps or forms.

This table lists the valid date and time formats.

Format	Description
YYMMDD	Two-digit year, two-digit month, two-digit day
MMDDYY	Two-digit month, two-digit day, last two digits of year (example: 121599)
YYYYMMDD	Four-digit year, two-digit month, two-digit day (example: 19991215)
DDMMYYYY	Two-digit day, two-digit month, four-digit year (example: 15121999)
MMDDYYYY	Two-digit month, two-digit day, four-digit year (example: 12151999)
DDMMYY	Two-digit day, two-digit month, last two digits of year (example: 151299)
YYMMMDD	Last two digits of year, three-letter abbreviation of the month, two-digit day (example: 99JAN02)
DDMMMYY	Two-digit day, three-letter https://greecepowerballresults.co.za/abbreviation of the month, last two digits of year (example: 02JAN99)
MMMDDYY	Three-letter abbreviation of the month, two-digit day, last two digits of year (example: JAN0299)
YYYYMMMDD	Four-digit year, three-letter abbreviation of the month, two-digit day (example: 2003JUL04)
DDMMMYYYY	Two-digit day, three-letter abbreviation of the month, four-digit year (example: 04JUL2003)
MMMDDYYYY	Three-letter abbreviation of the month, two-digit day, four-digit year (example: JUL042003)
YYDDD	Last two digits of year, three-digit Julian day (example: 99349 for the 349th day of 1999)
DDDYY	Three-digit Julian day, last two digits of year (example: 34999)
YYYYDDD	Four-digit year, three-digit Julian day (example: 1999349)
DDDYYYY	Three-digit Julian day, four-digit year (example: 3491999)
YY/MM/DD	Last two digits of year, separator, two-digit month, separator, twodigit day (example: 99/12/05)
DD/MM/YY	Two-digit day, separator, two-digit month, separator, last two digits of year (example: 05/12/99)
MM/DD/YY	Two-digit month, separator, two-digit day, separator, last two digits of year (example: 12/15/99)
YYYY/MM/DD	Four-digit year, separator, two-digit month, separator, two-digit day (example: 1999/12/15)
DD/MM/YYYY	Two-digit day, separator, two-digit month, separator, four-digit year (example: 15/12/1999)
MM/DD/YYYY	Two-digit month, separator, two-digit day, separator, four-digit year (example: 12/15/1999)
YY/MMM/DD	YY/MMM/DD Two-digit year, separator, three-letter abbreviation of the month, separator, two-digit day (example: 99/JUL/20)
DD/MMM/YY	Two-digit day, separator, three-letter abbreviation of the month, separator, two-digit year (example: 20/JUL/99)
MMM/DD/YY	Three-letter abbreviation of the month, separator, two-digit day, separator, two-digit year (example: JUL/20/99)
YYYY/MMM/DD	Four-digit year, separator, three-letter abbreviation of the month, separator, two-digit day (example: 2003/JUL/25)
DD/MMM/YYYY	Two-digit day, separator, three-letter abbreviation of the month, separator, four-digit year (example: 25/JUL/2003)
MMM/DD/YYYY	Three-letter abbreviation of the month, separator, two-digit day, separator, four-digit year (example: JUL/25/2003)
YY/DDD	Last two digits of year, separator, three-digit Julian day (example: 99/349)
DDD/YY	Three-digit Julian day, separator, last two digits of year (example: 349/99)
YYYY/DDD	Four-digit year, separator, three-digit Julian day (example: 1999/ 349)
DDD/YYYY	Three-digit Julian day, separator, four-digit year (example: 349/ 1999)
MONTH	Month (example: December)
DAY	Day of the week (example: Friday)
HHMM	Two-digit hour, two-digit minutes (example: 0330 for 30 minutes past 3 o'clock)
HHMMSS	Two-digit hour, two-digit minutes, two-digit seconds (example: 033045 for 30 minutes and 45 seconds past 3 o’clock)
HH:MM	Two-digit hour, separator, two-digit minutes (example: 03:30)
HH:MM:SS	Two-digit hour, separator, two-digit minutes, separator, two-digit seconds (example: 03:30:45)
YYYYMMDDTHH MMSS.mmmZ	ISO-8601 format: Four-digit year, two-digit month, two-digit day, T (time) indicator, two-digit hour, two-digit minutes, two-digit seconds in Universal Time (also called Zulu Time or Greenwich Mean Time), Z (Zulu time) indicator (example: 20031209T123000.000Z)
YYYYMMDDZ	ISO-8601 date format: Four-digit year, two-digit month, two-digit day, Z (Zulu time) indicator (example: 20031209Z)
MM/DD/YY HH:MM:SS	Two-digit month, separator, two-digit day, separator, last two digits of year, two-digit hour, separator, two-digit minutes, separator, two-digit seconds (example: 12/15/99 03:30:45)
YYMMDD HHMMSS	Last two digits of year, two-digit month, two-digit day, two-digit hour, two-digit minutes, two-digit seconds (example: 991025 033045)
YYYY-MMDDTHH: MM:SS	Four-digit year, separator, two-digit month, separator, two-digit day, T represents a blank separator, two-digit hour, separator, twodigit minutes, separator, two-digit seconds (example: 2002-02-02 03:30:45)
YYYY-MM-DD	Four-digit year, separator, two-digit month, separator, two-digit day (example: 2002-02-02)
YYYY-MM	Four-digit year, separator, two-digit month (example: 2002-02)
YYYY	Four-digit year (example: 2002)
--MM-DD	Two dashes, two-digit month, separator, two-digit day (example: - -12-02)
---DD	Three dashes, two-digit day (example: ---02)

[Kronos]

Hi,

You may also refer to this article for the available functions in displaying the day, month, and year from a Date/Time field as an integer:

Function Reference

[autonumber]

Hi! Just to update this post, if you want to format your date field by month and year only, you use this formula: 

CONVERT(VARCHAR,(Month([@field:Date_]))) + '-' + CONVERT(VARCHAR,( Year([@field:Date_])))