Functions/Dates & time/last_day
Dates & time Scalar

last_day()

Returns the last day of the month

Arguments & returns

3 overloads

last_day(ts)

Scalar
Overload link ts Returns
#01 DATE DATE
#02 TIMESTAMP DATE
#03 TIMESTAMP WITH TIME ZONE DATE

Example

last_day example
SQL
Haybarn WASM 1.5.5-rc3 · In your browser

Engine example · Run to view results.

In other engines

BigQuery

SELECT LAST_DAY(CAST('2008-11-25' AS DATE), MONTH)
In DuckDB
SELECT LAST_DAY(CAST('2008-11-25' AS DATE))

SQLGlot example ↗

Presto

SELECT LAST_DAY_OF_MONTH(CAST('2008-11-25' AS DATE))
In DuckDB
SELECT LAST_DAY(CAST('2008-11-25' AS DATE))

SQLGlot example ↗

SQL Server

EOMONTH(GETDATE())
In DuckDB
LAST_DAY(CAST(CURRENT_TIMESTAMP AS DATE))

SQLGlot example ↗

Snowflake

LAST_DAY(CAST('2023-04-15' AS DATE))
In DuckDB
LAST_DAY(CAST('2023-04-15' AS DATE))

SQLGlot example ↗

Used in larger rewrites 1

These examples use last_day as one part of a larger SQL translation.

Apache Sparkmonths_between()

SELECT MONTHS_BETWEEN('1997-02-28 10:30:00', '1996-10-30')
In DuckDB
SELECT DATE_DIFF('MONTH', CAST('1996-10-30' AS DATE), CAST('1997-02-28 10:30:00' AS DATE)) + CASE WHEN DAY(CAST('1997-02-28 10:30:00' AS DATE)) = DAY(LAST_DAY(CAST('1997-02-28 10:30:00' AS DATE))) AND DAY(CAST('1996-10-30' AS DATE)) = DAY(LAST_DAY(CAST('1996-10-30' AS DATE))) THEN 0 ELSE (DAY(CAST('1997-02-28 10:30:00' AS DATE)) - DAY(CAST('1996-10-30' AS DATE))) / 31.0 END

SQLGlot example ↗