Skip to content

Instantly share code, notes, and snippets.

@alancoleman

alancoleman/greatest_least_date_columns.sql

Last active December 18, 2015 00:39
Show Gist options
  • Save alancoleman/5698485 to your computer and use it in GitHub Desktop.
Save alancoleman/5698485 to your computer and use it in GitHub Desktop.
Download ZIP
There are three date columns in the table, each can contain any date or be null. This query will return all records between a date range when at least one of the date columns has a value, taking the highest or lowest value date from the row.
Raw
greatest_least_date_columns.sql
SELECT date1, date2, date3
FROM dates AS d
WHERE GREATEST(IFNULL(d.date1,MAKEDATE(2099,365)),
IFNULL(d.date2,MAKEDATE(2099,365)),
IFNULL(d.date3,MAKEDATE(2099,365))) <= '2013-06-03 16:08:46'
AND
LEAST(IFNULL(d.date1,MAKEDATE(1900,365)),
IFNULL(d.date2,MAKEDATE(1900,365)),
IFNULL(d.date3,MAKEDATE(1900,365))) >= '2008-04-01 16:08:46'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment