To give a date field a specific format you will need to enter the code to format the field.
You will either need to create a New Report by clicking the New Report button at the top of the Reports screen (Menu option, Tools > Reporting). Please click here for steps on creating a Standard Report.
Or select the Edit button on a Report which you have created previously.
Firstly, you must ensure that the date field you are looking to format is included in the Report. If desired you can tick/check the box to hide the field so that it does not appear on the Report in the wrong format.
You will then need to go into the Special folder and select the Query field which will be added to the right-hand list.
You can rename the Query under the Heading so it is clear what date field you have formatted.
Click on the gear icon to edit the Query.
In the popup add the following to the text box -
(DATE_FORMAT([FIELDNAME],'%d/%m/%y %h:%i %p'))
[FIELDNAME] will need to be replaced with the name of the field you are looking to format. In this example, I want to format the NextDate.
The example above will format a date into a standard UK DD/MM/YY HH:MM AM/PM (i.e. 19/07/16 03:30 PM) format - this part of the format is controlled by '%d/%m/%y %h:%i %p'
A list of common formatting codes:
Character | Description | Example |
Day |
|
|
%d | Day of the month, 2 digits with leading zeros | 01 to 31 |
%a | A textual representation of a day, three letters | Mon through Sun |
%e | Day of the month without leading zeros | 1 to 31 |
%W | A full textual representation of the day of the week | Monday through Sunday |
%D | Day stamp with the English ordinal suffix for the day of the month, 2 characters | 1st, 2nd, 3rd or 14th. |
Month |
|
|
%M | A full textual representation of a month | January through December |
%m | Numeric representation of a month, with leading zeros | 01 through 12 |
%b | A short textual representation of a month, three letters | Jan through Dec |
%c | Numeric representation of a month, without leading zeros | 1 through 12 |
Year |
|
|
%Y | A full numeric representation of a year, 4 digits | Examples: 1999 or 2003 |
%y | A two-digit representation of a year | Examples: 99 or 03 |
Time |
|
|
%r | Full-time stamp with AM/PM | 02:45:00PM |
%p | AM/PM for 12hour time stamp | AM or PM |
%h | 12-hour format of an hour with leading zeros | 01 through 12 |
%H | 24-hour format of an hour with leading zeros | 00 through 23 |
%i | Minutes with leading zeros | 00 through 59 |
Another example of how you can format a Date Field using the codes above -
(DATE_FORMAT([FIELDNAME],'%W %D of %M %Y at %h:%i %p'))
