CODEWITHSUNDEEP
sql
valenon
valenon

Reputation: 21

What is wrong with my date ranges in my CASE statement

SELECT animalid,
CASE WHEN birthdate BETWEEN '12/31/21'  AND '12/31/2017' THEN 0
     WHEN birthdate BETWEEN '12/31/2017' AND '12/31/2014' THEN 100
     WHEN birthdate BETWEEN '12/31/2014' AND '12/31/2009' THEN 150
     WHEN birthdate BETWEEN '12/31/2009' AND '12/31/2006' THEN 200
     ELSE 300 END AS age_cost
FROM animals

When I process this all of my results get a cost of 300. What is not working in my CASE statement date ranges?

Upvotes: 0

Views: 253

Answers (2)

LukStorms
LukStorms

Reputation: 29667

In some databases it's better to use date literals in the YYYY-MM-DD format.

This will avoid a wrong interpretation of the date stamp.

F.e. The text '02/06/04'
Is it "February 6, 2004"?
Or "June 2, 2004"?
Or "April 6, 2002"?
Or "April 2, 2006"?

But '2004-06-02' will be understood correctly.

Also, the syntax for BETWEEN expects the first date to be lower than the second.

This would work in Postgresql & Sqlite :

SELECT animalid,
CASE WHEN birthdate BETWEEN '2018-01-01' AND '2021-12-31' THEN 0
     WHEN birthdate BETWEEN '2015-01-01' AND '2017-12-31' THEN 100
     WHEN birthdate BETWEEN '2010-01-01' AND '2014-12-31' THEN 150
     WHEN birthdate BETWEEN '2007-01-01' AND '2009-12-31' THEN 200
     ELSE 300 END AS age_cost
FROM animals

Upvotes: 1

Chris Albert
Chris Albert

Reputation: 2507

You have the end date first. You need to have the beginning of your range first.

On another note I would reconsider your use of BETWEEN. See this blog post for more info on why.

SELECT
    animalid
    , CASE
          WHEN birthdate
               BETWEEN '12/31/2017' AND '12/31/2021'
              THEN 0
          WHEN birthdate
               BETWEEN '12/31/2014' AND '12/31/2017'
              THEN 100
          WHEN birthdate
               BETWEEN '12/31/2009' AND '12/31/2014'
              THEN 150
          WHEN birthdate
               BETWEEN '12/31/2006' AND '12/31/2009'
              THEN 200
          ELSE 300
          END AS age_cost
FROM
    animals;

Upvotes: 1

Related Questions