Previous pageData Integration Next page
Expressions - Date Format 

The DATE_FORMAT function returns a string based on the supplied datetime value

DATE_FORMAT(datetime, format)

Where [format] is a string made up of one or more of the following characters:

CategoryCharacterDescriptionOutput examples
DaysdDay of the month, 2 digits with leading zeros 01 to 31
DA textual representation of a day, three letters* Mon to Sun
jDay of the month without leading zeros1 to 31
l (lowercase 'L')A full textual representation of the day of the week* Sunday to Saturday
wNumeric representation of the day of the week0 (for Sunday) to 6 (for Saturday)
zDay of the year, 3 digits with leading zeroes001 to 365
WeeksWISO-8601 week number of year, weeks starting on Monday42 (the 42nd week in the year)
MonthsFA full textual representation of a month* January to December
mNumeric representation of a month, with leading zeros01 to12
MA short textual representation of a month, three letters* Jan to Dec
nNumeric representation of a month, without leading zeros1 to12
YearsoISO-8601 year number. This has the same value as Y, except that if the ISO week number (W) belongs to the previous or next year, that year is used instead.1999 or 2003
YA full numeric representation of a year, 4 digits1999 or 2003
yA two digit representation of a year99 or 03
TimeAUppercase Ante meridiem and Post meridiemAM or PM
g12-hour format of an hour without leading zeros1 to 12
G24-hour format of an hour without leading zeros0 to 23
h12-hour format of an hour with leading zeros01 to 12
H24-hour format of an hour with leading zeros00 to 23
iMinutes with leading zeros00 to 59
sSeconds, with leading zeros00 to 59


* The day and month names use the text specified in the Query's Behavior Editor. This allows you to translate them into different languages. The Behavior Editor fields can also be linked to take their values from themes - thus allowing you to provide different users with different languages depending on their individual preferences.


Using other characters
Including characters other than those listed above in the format string is nor supported: they may render verbatim, but if such a character is later used to provide a date expansion then the result will change.  Instead, you should use string concatenation, for example:

"Day: " || DATE_FORMAT([date], 'D')


Managing data