telly Posted January 25, 2020 Report Share Posted January 25, 2020 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. researched 1 Quote Link to comment Share on other sites More sharing options...
1 SunakoChan Posted January 25, 2020 Report Share Posted January 25, 2020 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. researched and vanderLeest 1 1 Quote Link to comment Share on other sites More sharing options...
0 vanderLeest Posted May 14, 2020 Report Share Posted May 14, 2020 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))) researched 1 Quote Link to comment Share on other sites More sharing options...
0 Kurumi Posted July 29, 2022 Report Share Posted July 29, 2022 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 researched 1 Quote Link to comment Share on other sites More sharing options...
0 MickyMartian232 Posted August 19, 2022 Report Share Posted August 19, 2022 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) Tubby and researched 2 Quote Link to comment Share on other sites More sharing options...
0 Kronos Posted April 14, 2023 Report Share Posted April 14, 2023 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 researched 1 Quote Link to comment Share on other sites More sharing options...
0 autonumber Posted December 4, 2023 Report Share Posted December 4, 2023 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_]))) Quote Link to comment Share on other sites More sharing options...
Question
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.
Link to comment
Share on other sites
6 answers to this question
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.