Date Format Specifier Characters in Beast Mode

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.

Note: In a Beast Mode calculation using a TIME_FORMAT function, you can specify the format to use for time values in a time column by specifying the column and the time string, as in TIME_FORMAT(`datetimecolumn`,'format') where datetimecolumn is the column containing a time value and where format is the string containing specifier characters to use in formatting the time value.

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
DATE_FORMAT(`MyDateColumn`,'%m-%d-%Y')	`11-04-2008`
DATE_FORMAT(`MyDateColumn`,'%d %b %y')	`04 Nov 08`
DATE_FORMAT(`MyDateColumn`,'%d %b %Y %T')	`04 Nov 2008 11:45:34`
DATE_FORMAT(`MyDateColumn`, '%m/%d/%Y')	`01/13/2013`
DATE_FORMAT(`MyDateColumn`, '%M %d, %Y')	`August 1, 2013`
DATE_FORMAT(`MyDateColumn`, '%b %d, %Y')	`Aug 1, 2013`
DATE_FORMAT(`MyDateColumn`,'%b %d %Y %h:%i %p')	`Nov 04 2008 11:45 PM`
DATE_FORMAT(`MyDateColumn`,'%Y-%m-%d %h:%i:%s')	`2013-11-06 09:38:10`
DATE_FORMAT(STR_TO_DATE(`MyDateColumn`,'%m,%d,%Y'),'%m/%d/%Y')	`11/04/2008`

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
`%a`	Abbreviated weekday name (Sun..Sat)	DATE_FORMAT(`MyDate`,'%a')	Mon
`%b`	Abbreviated month name (Jan..Dec)	DATE_FORMAT(`MyDate`,'%b')	Apr
`%d`	Day of month, numeric (00-31)	DATE_FORMAT(`MyDate`,'%d')	13
`%f`	Microseconds (000000..999999)	DATE_FORMAT(`MyDate`,'%f')	300000
`%H`	Hour (00-23)	DATE_FORMAT(`MyDate`,'%H')	23
`%h`	Hour (01-12)	DATE_FORMAT(`MyDate`,'%h')	11
`%i`	Minutes, numeric (00-59)	DATE_FORMAT(`MyDate`,'%i')	44
`%j`	Day of year (001-366)	DATE_FORMAT(`MyDate`, '%j')	105
`%M`	Month name (January..December)	DATE_FORMAT(`MyDate`,'%M')	April
`%m`	Month, numeric (00-12)	DATE_FORMAT(`MyDate`,'%m')	04
`%p`	AM or PM	DATE_FORMAT(`MyDate`,'%p')	PM
`%r`	Time, 12-hour (hh:mm:ss followed by AM or PM)	DATE_FORMAT(`MyDate`,'%r')	11:44:15 PM
`%s`	Seconds (00-59)	DATE_FORMAT(`MyDate`,'%s')	15
`%T`	Time, 24-hour (hh:mm:ss)	DATE_FORMAT(`MyDate`,'%T')	23:44:15
`%v`	ISO week number of the year (the first Thursday of the new year is in week 1)	DATE_FORMAT(`MyDate`,'v')	16
`%W`	Weekday name (Sunday..Saturday)	DATE_FORMAT(`MyDate`,'%W')	Monday
`%w`	Day of the week (1-7; Sunday is 1)	DATE_FORMAT(`MyDate`,'w')	2
`%x`	ISO year (4 or more digits)	DATE_FORMAT(`MyDate`,'x')	2013
`%Y`	Year (4 or more digits)	DATE_FORMAT(`MyDate`,'%Y')	2013
`%y`	Last two digits of year	DATE_FORMAT(`MyDate`,'%y')	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.

Note: In Beast Mode calculations, the unit type value is case insensitive.

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'