The max function in SQL allows you to fetch the greatest value from a set of values in a given column.
In most cases, we use the max function with numeric data types such as integers, floats, decimals, money, etc.
However, did you know you can use the max function with date types? Using the max() function in SQL with date data types returns the latest date.
This can be useful when filtering the recently added record in a given table.
Example Usage
Let us assume we have a table as shown below:
Check the code for the table below:
id serial primary key,
full_name varchar(255),
email varchar(255),
department varchar(100),
start_date date,
active bool,
category varchar(50)
);
insert into employees (full_name, email, department, start_date, active, category)
values
('Meghan Edwards', '[email protected]', 'Game Development', '2016-06-22', TRUE, 'DEV-G1'),
('Sam Davis', '[email protected]', 'Game Development', '2014-01-04', FALSE, 'DEV-G1'),
('Taylor Miller', '[email protected]', 'Game Development', '2019-10-06', TRUE, 'DEV-G1'),
('Wilson Smitty', '[email protected]', 'Database Developer', '2016-12-23', TRUE, 'DEV-DB1'),
('Barbara Moore', '[email protected]', 'Database Developer', '2015-12-23', TRUE, 'DEV-DB1'),
('Raj Chan', '[email protected]', 'Database Developer', '2017-12-23', FALSE, 'DEV-DB1'),
('Susan Brown', '[email protected]', 'DevOps Engineer', '2011-12-23', TRUE, 'DEV-OPS1'),
('Marial Anderson', '[email protected]', 'DevOps Engineer', '2015-12-23', TRUE, 'DEV-OPS1'),
('Nancy Thomas', '[email protected]', 'DevOps Engineer', '2014-12-23', FALSE, 'DEV-OPS1');
We can select the latest date from the start_date column as shown:
This should return the latest date as shown:
Unfortunately, you cannot use the max(date) function with the where clause. Doing so will result in an error as shown:
This is because SQL does not allow aggregate functions in the where clause.
Inference
This article covers the basics of using the max function with a date type in SQL. For example, using the max() function with date type returns the latest date from the given set.
