select document based on date range

Question

Structure

CREATE TABLE `template` (
    `name` VARCHAR(16) NOT NULL COLLATE 'utf8_unicode_ci',
    `valid` DATE NULL DEFAULT NULL,
    UNIQUE INDEX `name` (`name`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB;

Data

I am trying to select right template name based on date. "Valid" column represents date till template is valid. If Template have 0000-00-00 or NULL valid date, that means that this template is current (latest). Template names does not suggest order (they can be random string).

Scenario:

If I have user, that will be created let's say 2020-01-01, then select should pick template with name "yes_4".

Scenario:

User was created in 2018-01-01, then template selected should have name "agreement_x"

Scenario:

User created in 2019-01-15 will select row with name "template_2"

So far:

:date = - user created

query

SELECT * FROM `template` 
WHERE DATE(`valid`) < DATE(:date) 
ORDER BY DATE(`valid`) DESC 
LIMIT 1

Answer 1

Something like

(   SELECT * 
    FROM `template` 
    WHERE DATE(`valid`) >= DATE(:date) 
    ORDER BY DATE(`valid`) 
    LIMIT 1
)
UNION ALL
(   SELECT * 
    FROM `template` 
    WHERE DATE(`valid`) <= '1970-01-01' OR `valid` IS NULL
)
ORDER BY `valid` DESC 
LIMIT 1

or

SELECT * 
FROM `template` 
WHERE CASE WHEN DATE(`valid`) > '1970-01-01'
           THEN `valid`
           ELSE '2038-01-19'
      END >= DATE(:date) 
ORDER BY CASE WHEN DATE(`valid`) > '1970-01-01'
              THEN `valid`
              ELSE '2038-01-19'
         END
LIMIT 1

and so on...