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. 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. vanderLeest 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))) Quote Link to comment Share on other sites More sharing options...
0 Meekeee 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 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 1 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
4 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.