Error when taking the min between two dates

Question

DROP TABLE IF EXISTS tmp1_variables;
CREATE TEMPORARY TABLE tmp1_variables AS (
SELECT
    '2016-10-29'::date as start_date,
'2017-01-28'::date as end_date2,
dateadd(day,-13,getdate())::date as end_date);


SELECT cobrand_id, sum(calc) AS a, sum(count) AS b FROM jwn_calc s, tmp1_variables
        where s.optimized_transaction_date > start_date
AND s.optimized_transaction_date <= min(end_date,end_date2)

I'm specifically getting the error at the min(end_date,end_date2), with error:

[42883] ERROR: function min(date, date) does not exist Hint: No function matches the given name and argument types. You may need to add explicit type casts.

`s.optimized_transaction_date <= case when end_date < end_date2 then end_date else end_date2 end` — Pரதீப், Jan 09 '17 at 01:30
You could rewrite as: `... AND s.optimized_transaction_date <= end_date AND s.optimized_transaction_date <= end_date2)` (If it's less than the smaller of 2 dates, it must be less than both of them.) — Disillusioned, Jan 09 '17 at 02:54

score 12 · Accepted Answer · edited May 23 '17 at 12:07

Use LEAST() instead of MIN():

SELECT cobrand_id,
       SUM(calc) AS a,
       SUM(count) AS b
FROM jwn_calc s
INNER JOIN tmp1_variables t
    ON s.optimized_transaction_date > t.start_date AND
       s.optimized_transaction_date <= LEAST(t.end_date, t.end_date2)

Note that I have replaced your implicit join with an explicit INNER JOIN. As a general rule, you should avoid using commas in the FROM clause.

Read here for more information on Postgres' LEAST() function

Error when taking the min between two dates

1 Answers1