0

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

enter image description here

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
1
  • Since valid is of type DATE, there is no need for applying DATE() to it.
    – Rick James
    Mar 4, 2019 at 22:38

1 Answer 1

0

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...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.