Calendar, conditions, and conversions

Use these 19 SQL functions to group dates, calculate conditional metrics, clean text, and convert values.

Function names are case-insensitive. Supply the exact argument count shown. These functions do not accept *, DISTINCT, or an OVER clause. They can be nested, but must still obey SQL grouping rules.

Calendar buckets

Buckets preserve the year. This matters when comparing several years of data: MONTH(value) means the start of that month, not a numeric month index.

MONTH(value)

Groups dates into calendar months, using date truncation after a date cast. A September 2026 input belongs to the bucket beginning September 1, 2026.

SELECT MONTH(order_date) AS month, SUM(revenue) AS revenue
FROM lake.uploads.orders
GROUP BY MONTH(order_date)
ORDER BY month;

YEAR(value)

Returns the calendar-year bucket. Values within 2026 belong to the bucket beginning January 1, 2026.

QUARTER(value)

Returns the first date of the calendar quarter: January 1, April 1, July 1, or October 1. It is not a fiscal-quarter calculation.

WEEK(value)

Returns the week bucket beginning Monday. A week can begin in the previous calendar year. Use the bucket date as the grouping key rather than combining it with an independently extracted year.

DAY(value)

Casts the input to a calendar date. Normalize timestamps to the intended reporting timezone before relying on a day boundary.

Calendar labels and numbers

MONTH_LABEL(value)

Returns a year-month text label such as 2026-09. Use it for display, or use MONTH when you need a date-like grouping key.

MONTH_NUMBER(value)

Returns the month number, 1 through 12. Unlike MONTH, this merges the same month across years when used alone in a grouping.

YEAR_NUMBER(value)

Returns the numeric calendar year.

QUARTER_NUMBER(value)

Returns 1 through 4 for the calendar quarter.

WEEKDAY_NUMBER(value)

Returns Monday as 1 through Sunday as 7.

SELECT MONTH_LABEL(order_date) AS month_label,
       YEAR_NUMBER(order_date) AS year_number,
       QUARTER_NUMBER(order_date) AS quarter_number,
       WEEKDAY_NUMBER(order_date) AS weekday_number
FROM lake.uploads.orders
LIMIT 20;

Conditional aggregates

COUNT_IF(condition)

Counts rows where the condition is true. False and null conditions do not count. An empty input produces zero for an ungrouped count.

SUM_IF(value, condition)

Sums matching values. Returns zero when the sum has no non-null matching values. The value comes first and the condition second.

AVG_IF(value, condition)

Averages matching values. Returns null when there are no non-null matching values.

SELECT country,
       COUNT_IF(status = 'completed') AS completed_orders,
       SUM_IF(revenue, status = 'completed') AS completed_revenue,
       AVG_IF(revenue, status = 'completed') AS average_completed_order
FROM lake.uploads.orders
GROUP BY country;

These are aggregate expressions. Add other selected columns to GROUP BY. Use the function name COUNT_IF with an underscore.

Safe ratios

SAFE_DIVIDE(numerator, denominator)

Uses floating-point division and returns null for a zero denominator. A null operand also gives null. SAFE_DIVIDE(1, 2) is 0.5, not integer zero.

PERCENT(numerator, denominator)

Returns the safe ratio multiplied by 100. PERCENT(1, 4) is 25, so do not multiply the result by 100 again.

SELECT PERCENT(COUNT_IF(status = 'completed'), COUNT(*)) AS completed_percent,
       SAFE_DIVIDE(SUM(revenue), COUNT(DISTINCT customer_id)) AS revenue_per_customer
FROM lake.uploads.orders;

A null ratio is different from a zero ratio. Use COALESCE only if showing zero for an undefined ratio is your intended business rule.

Text cleanup

BLANK_TO_NULL(value)

Trims text and turns an empty result into null. A null input stays null.

DEFAULT_IF_BLANK(value, fallback)

Trims the value and substitutes the fallback for null or blank text. Nonblank input is returned trimmed.

SELECT BLANK_TO_NULL(campaign_name) AS campaign,
       DEFAULT_IF_BLANK(country, 'Unknown') AS country
FROM lake.marketing.spend
LIMIT 20;

Tolerant conversion

SAFE_NUMBER(value)

Converts to a double-precision number; invalid input becomes null. It is not an exact-decimal money workbench and does not clean currency symbols or locale-specific separators for you.

SAFE_DATE(value)

Converts a date value using a tolerant date cast; invalid input becomes null. Prefer unambiguous ISO date strings such as 2026-09-15.

SELECT SAFE_DATE(order_date_text) AS order_date,
       SAFE_NUMBER(revenue_text) AS revenue
FROM lake.uploads.imported_orders
LIMIT 20;

Check for failed conversions before aggregation. A conversion returning null can make a later sum omit the affected rows.

Query planning

Apply date filters and select only the columns you need. Use Explain before a large query to review its data requirements and estimated usage. See cross-source querying when combining connectors.