Intro
In a Beast Mode calculation using a DATE_FORMAT function, you can specify the format to use for a date or time column by specifying the column and the date or time string, as in DATE_FORMAT(`datecolumn`,'format') where datecolumn is the column containing a date value and where format is the string containing specifier characters to use in formatting the date value.
For example, using DATE_FORMAT(`MyDate`,'%Y-%m-%d %h:%i %p'), the date in the MyDate date column uses this format: 2013-04-17 10:10 AM.
The "%" character is required before format specifier characters.
If necessary, you can convert date string values in columns to datetime values using the STR_TO_DATE function.
The format specifiers used in DATE_FORMAT may be used with TIME_FORMAT, but specifiers other than hours, minutes, seconds and microseconds produce a NULL value or 0.
Sample Formats
Format |
Result |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
For more information about adding Beast Mode calculations, see Adding a Beast Mode Calculation to Your Chart.
Date format specifiers
All examples assume a date and time of April 15th, 2013, at 11:44:15 PM.
Specifier |
Description |
Sample |
Result |
---|---|---|---|
|
Abbreviated weekday name (Sun..Sat) |
|
Mon |
|
Abbreviated month name (Jan..Dec) |
|
Apr |
|
Day of month, numeric (00-31) |
|
13 |
|
Microseconds (000000..999999) |
|
300000 |
|
Hour (00-23) |
|
23 |
|
Hour (01-12) |
|
11 |
|
Minutes, numeric (00-59) |
|
44 |
|
Day of year (001-366) |
|
105 |
|
Month name (January..December) Note: This option is padded with trailing whitespace to 9 characters.
|
|
April |
|
Month, numeric (00-12) |
|
04 |
|
AM or PM |
|
PM |
|
Time, 12-hour (hh:mm:ss followed by AM or PM) |
|
11:44:15 PM |
|
Seconds (00-59) |
|
15 |
|
Time, 24-hour (hh:mm:ss) |
|
23:44:15 |
|
ISO week number of the year (the first Thursday of the new year is in week 1) |
|
16 |
|
Weekday name (Sunday..Saturday) |
|
Monday |
|
Day of the week (1-7; Sunday is 1) |
|
2 |
|
ISO year (4 or more digits) |
|
2013 |
|
Year (4 or more digits) |
|
2013 |
|
Last two digits of year |
|
13 |
Unit type values
You can specify unit type values for date or datetime expressions in ADDDATE, DATE_ADD, DATE_SUB, and SUBDATE functions, including those listed in the following table.
Unit Type Value |
Expected Expression Format |
---|---|
MICROSECOND |
MICROSECONDS |
SECOND |
SECONDS |
MINUTE |
MINUTES |
HOUR |
HOURS |
DAY |
DAYS |
WEEK |
WEEKS |
MONTH |
MONTHS |
QUARTER |
QUARTERS |
YEAR |
YEARS |
SECOND_MICROSECOND |
'SECONDS.MICROSECONDS' |
MINUTE_MICROSECOND |
'MINUTES:SECONDS.MICROSECONDS' |
MINUTE_SECOND |
'MINUTES:SECONDS' |
HOUR_MICROSECOND |
'HOURS:MINUTES:SECONDS.MICROSECONDS' |
HOUR_SECOND |
'HOURS:MINUTES:SECONDS' |
HOUR_MINUTE |
'HOURS:MINUTES' |
DAY_MICROSECOND |
'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS' |
DAY_SECOND |
'DAYS HOURS:MINUTES:SECONDS' |
DAY_MINUTE |
'DAYS HOURS:MINUTES' |
DAY_HOUR |
'DAYS HOURS' |
YEAR_MONTH |
'YEARS-MONTHS' |
Comments
1 comment
A customer called in wondering about their fiscal calendar/timezone with Beastmodes and their functions. I believe that it should be mentioned in this article that Beastmodes are not effected by either of these if they are changed in the users instance.
Please sign in to leave a comment.