MySQL: Datetime – OSKAR

This requires Builder privileges for utilization.

Function Returns	Syntax	Data Type	Results Example
Calendar Year	YEAR(datetime_field)	INT	2015
Calendar Quarter	QUARTER(datetime_field)	INT	2
Day of the Year	DAYOFYEAR(datetime_field)	INT	365
Month Number	MONTH(datetime_field)	INT	12
Month Name	MONTHNAME(datetime_field)	STRING	February
Short Month Name	LEFT(MONTHNAME(datetime_field),3)	STRING	Feb
First Day of Month	SUBDATE(datetime_field, (DAY(datetime_field)-1))	DateTime	2010-01-01
ISO Week Number	WEEK(datetime_field, 3)	INT	42
Name of Weekday	DAYNAME(datetime_field)	STRING	Friday
Short Name of Weekday	LEFT(DAYNAME(datetime_field), 3)	STRING	Fri
Day in Week Number	DAYOFWEEK(datetime_field)	INT	5
Day in Month	DAYOFMONTH(datetime_field)	INT	25
Boolean Weekend	CASE DAYOFWEEK(datetime_field) WHEN 1 THEN 1 WHEN 7 THEN 1 ELSE 0 END	INT	1 = True 0 = False
Hour(s) in Day	HOUR(datetime_field)	INT	13
Minute(s) in Hour	MINUTE(datetime_field)	INT	56
Second(s) in Minute	SECOND(datetime_field)	INT	42
Fiscal Quarter (July - June)	CASE WHEN MONTH(datetime_field) BETWEEN 1 AND 3 THEN CONCAT('FY', CONVERT(YEAR(datetime_field) - 0, char(4)),'-', 'Q3') WHEN MONTH(datetime_field) BETWEEN 4 AND 6 THEN CONCAT('FY', CONVERT(YEAR(datetime_field) - 0, char(4)),'-', 'Q4') WHEN MONTH(datetime_field) BETWEEN 7 AND 9 THEN CONCAT('FY', CONVERT(YEAR(datetime_field) + 1, char(4)),'-', 'Q1') WHEN MONTH(datetime_field) BETWEEN 10 AND 12 THEN CONCAT('FY', CONVERT(YEAR(datetime_field) + 1, char(4)),'-', 'Q2') END	STRING	FY2014-Q3
Fiscal Quarter (October - September)	CASE WHEN MONTH(datetime_field) BETWEEN 1 AND 3 THEN CONCAT('FY', CONVERT(YEAR(datetime_field) - 0, char(4)),'-', 'Q2') WHEN MONTH(datetime_field) BETWEEN 4 AND 6 THEN CONCAT('FY', CONVERT(YEAR(datetime_field) - 0, char(4)),'-', 'Q3') WHEN MONTH(datetime_field) BETWEEN 7 AND 9 THEN CONCAT('FY', CONVERT(YEAR(datetime_field) - 0, char(4)),'-', 'Q4') WHEN MONTH(datetime_field) BETWEEN 10 AND 12 THEN CONCAT('FY', CONVERT(YEAR(datetime_field) + 1, char(4)),'-', 'Q1') END	STRING	FY2014-Q3
Date Difference	TIMESTAMPDIFF(DAY, datetime_field_2, datetime_field_1)	INT	36

All queries in this guide are provided as-is for reference purposes. iDashboards Technical Support Team will not assist with authoring new queries. Please check out Oracle's MySQL 11.2.2 The DATE, DATETIME, and TIMESTAMP Types documentation for more information on anything concerning Datetime.

Related articles