Extract Date Components
Extract individual components from dates and datetimes — year, month, day, hour, minute, or second. Use these functions to filter by time period, display formatted dates, or build conditional logic based on specific date parts.
Functions
YEAR — Extract the year (e.g., 2024)
MONTH — Extract the month (1-12)
DAY — Extract the day of month (1-31)
HOUR — Extract the hour (0-23)
MINUTE — Extract the minute (0-59)
SECOND — Extract the second (0-59)
YEAR
Syntax: YEAR(date)
Extracts the year from a date as a 4-digit number.
Examples
Display copyright year:
`© ${YEAR(CURRDATE())} Your Company`Filter orders by year:
YEAR(ORDER_DATE) = 2024Calculate years since event:
YEAR(CURRDATE()) - YEAR(EVENT_DATE)Build date from components:
DATE(YEAR(CURRDATE()), 4, 15) // Tax deadline this yearReturns a number (2024), not a string. Combine with template literals for display.
MONTH
Syntax: MONTH(date)
Extracts the month as a number from 1-12 (January = 1, December = 12).
Examples
Check if in Q4:
MONTH(CURRDATE()) >= 10Calculate quarter:
CEILING(MONTH(CURRDATE()) / 3)Birthday month check:
IF(MONTH(CURRDATE()) = MONTH(BIRTH_DATE), "Birthday month!", "")Seasonal pricing:
IF(MONTH(CURRDATE()) >= 6 AND MONTH(CURRDATE())Returns 1 for January, 12 for December. Not 0-indexed like some programming languages.
DAY
Syntax: DAY(date)
Extracts the day of the month (1-31).
Examples
Check if payment is due (1st of month):
DAY(CURRDATE()) = 1End-of-month logic:
DAY(CURRDATE()) >= 28Build first day of month:
DATE(YEAR(CURRDATE()), MONTH(CURRDATE()), 1)Display formatted date:
`${MONTH(EVENT_DATE)}/${DAY(EVENT_DATE)}/${YEAR(EVENT_DATE)}`HOUR
Syntax: HOUR(dateTime)
Extracts the hour in 24-hour format (0-23).
Examples
Check business hours (9 AM - 5 PM):
HOUR(CURRDATETIME()) >= 9 AND HOUR(CURRDATETIME()) < 17After-hours surcharge:
IF(HOUR(CURRDATETIME()) >= 18 OR HOUR(CURRDATETIME()) < 8,
PRICE * 1.5,
PRICE)Display 12-hour format:
IF(HOUR(TIME) > 12, HOUR(TIME) - 12, HOUR(TIME))Morning vs afternoon:
IF(HOUR(CURRDATETIME()) < 12, "Good morning", "Good afternoon")Returns 0-23: midnight = 0, 1 PM = 13, 11 PM = 23.
MINUTE
Syntax: MINUTE(dateTime)
Extracts the minute (0-59).
Examples
Display time:
`${HOUR(TIME)}:${MINUTE(TIME)}`Round to nearest hour:
IF(MINUTE(TIME) >= 30, HOUR(TIME) + 1, HOUR(TIME))Check exact time:
HOUR(CURRDATETIME()) = 14 AND MINUTE(CURRDATETIME()) = 30Combine with HOUR for complete time display or validation.
SECOND
Syntax: SECOND(dateTime)
Extracts the second (0-59).
Examples
Precise timestamp:
`${HOUR(TIME)}:${MINUTE(TIME)}:${SECOND(TIME)}`Countdown to exact time:
SECONDS_LEFT = 60 - SECOND(CURRDATETIME())Most business logic doesn't need seconds. Use HOUR and MINUTE for typical time displays.
Common Patterns
Copyright notice:
`© ${YEAR(CURRDATE())} Your Company`Format date (MM/DD/YYYY):
`${MONTH(DATE)}/${DAY(DATE)}/${YEAR(DATE)}`Quarterly grouping:
QUARTER = CEILING(MONTH(ORDER_DATE) / 3)
`Q${QUARTER} ${YEAR(ORDER_DATE)}`Birthday this year:
DATE(YEAR(CURRDATE()), MONTH(BIRTH_DATE), DAY(BIRTH_DATE))Business hours check:
IS_BUSINESS_HOURS = HOUR(CURRDATETIME()) >= 9 AND HOUR(CURRDATETIME()) < 17
IF(IS_BUSINESS_HOURS, "We're open", "Closed")First day of current month:
DATE(YEAR(CURRDATE()), MONTH(CURRDATE()), 1)Time display with AM/PM:
HOUR_12 = IF(HOUR(TIME) > 12, HOUR(TIME) - 12, IF(HOUR(TIME) = 0, 12, HOUR(TIME)))
AMPM = IF(HOUR(TIME) >= 12, "PM", "AM")
`${HOUR_12}:${MINUTE(TIME)} ${AMPM}`Common Mistakes
Month is 1-indexed: January = 1, not 0. MONTH never returns 0.
HOUR returns 24-hour format: 2 PM = 14, not 2. Convert for 12-hour display.
Using HOUR/MINUTE/SECOND with DATE: These require DATETIME, not DATE. Use CURRDATETIME or DATETIME().
Forgetting to pad single digits: MINUTE can return 5, not "05". Format for display if needed.
See Also
DATE · DATETIME · CURRDATE · CURRDATETIME · DATEDIFF · WEEKDAY