Case When statement for Dates in SQL Query

Question

I'm attempting to fix some of the dates I have in my SQL table. For context, I joined the two tables, "Trade Details" and "Trade Details 2" together. One of the columns in "Trade Details 2" is "START_DATE" with dates ranging back to the early 2000's in the format 2010-05-08. I'm looking to make every START_DATE before 2018-06-22 into 2018-06-22. Currently, my code is below:

SELECT "Trade Details 2".Portfolio, 
"Trade Details 2".CONTRACT_ID, 
DATE("Trade Details 2".START_DATE) as START_DATE, 
DATE(substr("Trade Details 2".MATURITY_DATE, 0, 5) || '-' || substr("Trade 
Details 2".MATURITY_DATE, 5,2) || '-' ||    substr("Trade Details 
2".MATURITY_DATE, 7, 9)) as MATURITY_DATE, 
"Trade Details 2".NOTIONAL1,
"Trade Details 2".CONTRACT_NPV,
"Trade Details".TERM
FROM "Trade Details 2" 
JOIN "Trade Details" 
WHERE "Trade Details 2".CONTRACT_ID="Trade Details".FCC_ID and
("Trade Details 2".NOTIONAL1 > "0.0") and
("Trade Details 2".MATURITY_DATE > DATE(substr('20180602', 0, 5) || '-' || 
substr('20180602', 5,2) || '-' ||    substr('20180602', 7, 9)) )
ORDER BY CONTRACT_ID asc

I believe that what I need is a CASE WHEN statement, where I say something like:

  CASE WHEN "Trade Details 2".START_DATE<2018-06-22 THEN =2018-06-22

However, when I tried this I got an error, and I'm guessing that some part of this line of code is wrong. However, since this is a date I don't know if a greater than/less than symbol will suffice, and I'm not sure where to put this statement, or even if this statement is totally correct. However, this is the gist of my question. This is different from other questions on here I believe because it's dealing with dates instead of simple numbers.

TLDR; Trying to turn dates prior to 2018-06-22 into 2018-06-22, but not sure how to do this

Answer 1

First, your CASE statement does not look right. It should be something like CASE WHEN condition THEN value ELSE value END (see SQLite Expressions).

Second, because SQLite does not have a "date" field type, you're probably using a string, so you need to convert your value to a date (see SQLite Date And Time Functions):

date(start_date)

For example, considering the following simplified data:

CREATE TABLE IF NOT EXISTS `trade_details` (
    `id` INTEGER,
    `start_date` TEXT,
    PRIMARY KEY(`id`)
);
INSERT INTO `trade_details` VALUES
    (1,'2018-06-01'),
    (2,'2018-06-22'),
    (3,'2018-06-23');

The following query should work:

SELECT
    id,
    start_date,
    CASE
        WHEN date(start_date) < date('2018-06-22')
        THEN '2018-06-22'
        ELSE start_date
    END AS modified_date
FROM trade_details;

Result:

+----+------------+---------------+
| id | start_date | modified_date |
+----+------------+---------------+
|  1 | 2018-06-01 | 2018-06-22    |
|  2 | 2018-06-22 | 2018-06-22    |
|  3 | 2018-06-23 | 2018-06-23    |
+----+------------+---------------+

You can also try and play with this SQL Fiddle: http://sqlfiddle.com/#!5/361ea/3/0