Case When expression 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 expression, 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. 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 expression, or even if this expression is totally correct.

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.

...there's a multi-argument MAX(..) function, will that not work? — Clockwork-Muse
– Clockwork-Muse, Commented Jul 9, 2018 at 17:18
@Clockwork-Muse so I would write Max("2018-06-22", "Trade Details 2".START_DATE" somewhere? Which line would I put this code on? — throway172
– throway172, Commented Jul 9, 2018 at 17:26
Tags (sql-server, db-browser-sqlite) are a bit misleading to me. Are you working with SQL Server or with SQLite? — user479288
– user479288, Commented Jul 9, 2018 at 17:30
Instead of the CASE statement, just part of the columns listed in the SELECT. Also, just noticed this, having tables with spaces is frowned upon. Not least because of the need to put delimiters around them every time... — Clockwork-Muse
– Clockwork-Muse, Commented Jul 9, 2018 at 17:32

score 0 · Accepted Answer · 2018-07-09 17:45:05Z

0

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

edited Jul 9, 2018 at 17:45

answered Jul 9, 2018 at 17:15

user479288

Sign up to request clarification or add additional context in comments.

9 Comments

throway172 Over a year ago

So would I put that stuff after my WHERE statement? So it's like WHERE"TD2"....substr('20180622', 7, 9)) ) And then the next line is

(CASE WHEN date("Trade Details 2".START_DATE) < '2018-06-22' THEN '2018-06-22' ELSE start_date END FROM "Trade Details 2"); ORDER BY CONTRACT_ID asc

Is that correct?

user479288 Over a year ago

No, it's inside after SELECT and before FROM. Use the CASE ... END statement as if it were a field.

throway172 Over a year ago

Gotcha, I have this right below "Trade Details".TERM. CASE WHEN date(START_DATE) < '2018-06-22' THEN '2018-06-22' ELSE start_date END AS modified date It's claiming there's a syntax error near "CASE" however. I tried to put paranthesis around CASE and modified date and that didn't work either

user479288 Over a year ago

Did you add a comma (,) after "Trade Details".TERM?

throway172 Over a year ago

I did, and now it just deleted my table when a ran the query. It says 0 rows returned. Before I added this qualification i'm making the post about, there were 19,000 thousand rows?

|

enegue · Accepted Answer · 2022-12-17 03:58:41Z

0

This is an old question, but I thought I'd suggest some things to simply your code.

SELECT
  td2.Portfolio, 
  td2.CONTRACT_ID, 
  td2.NOTIONAL1,td2.CONTRACT_NPV, td.TERM,
  CASE WHEN td2.START_DATE < '2018-06-02' 
       THEN '2018-06-02' 
       ELSE td2.START_DATE AS START_DATE
FROM "Trade Details 2" td2
JOIN "Trade Details" td
WHERE td2.CONTRACT_ID=td.FCC_ID AND (td2.NOTIONAL1 > "0.0")
ORDER BY CONTRACT_ID ASC

There is no need to convert the text dates to date values since the date format chosen will allow a simple string sort.
An 'alias' can be chosen for your tables, say 'td' and 'td2'. Doing this will clear up a lot of the clutter.

Hope this is useful to someone.

answered Dec 17, 2022 at 3:58

enegue

2912 silver badges7 bronze badges

Comments

Rajat Jaiswal · Accepted Answer · 2023-01-20 12:31:04Z

0

I was able to figure out. See below.

  select *
  ,case when  2015 >= year(startdate) and 2015 <= year(enddate) then 'yes' end as '2015'
  ,case when  2016 >= year(startdate) and 2016 <= year(enddate) then 'yes' end as '2016'
  ,case when  2017 >= year(startdate) and 2017 <= year(enddate) then 'yes' end as '2017'
  ,case when  2018 >= year(startdate) and 2018 <= year(enddate) then 'yes' end as '2018'
  ,case when  2019 >= year(startdate) and 2019 <= year(enddate) then 'yes' end as '2019'
  ,case when  2020 >= year(startdate) and 2020 <= year(enddate) then 'yes' end as '2020'
  ,case when  2021 >= year(startdate) and 2021 <= year(enddate) then 'yes' end as '2021'
  ,case when  2022 >= year(startdate) and 2022 <= year(enddate) then 'yes' end as '2022'
  from #Have
  order by ID

edited Jan 20, 2023 at 12:31

Rajat Jaiswal

6414 silver badges15 bronze badges

answered Jan 19, 2023 at 6:33

user12501522

515 bronze badges

Comments

gzzz · Accepted Answer · 2025-02-15 18:36:35Z

0

SELECT
    d2.portfolio,
    d2.contract_id,
    MAX(DATE(d2.start_date), DATE('2018-06-02')) AS start_date,
    DATE(d2.maturity_date)                       AS maturity_date,
    d2.notional1,
    d2.contract_npv,
    d.term
FROM "Trade Details 2" AS d2
JOIN "Trade Details"   AS d ON (d.contract_id = d2.fcc_id)
WHERE TRUE
    AND d2.notional1     >  0
    AND d2.maturity_date > '20180602'
ORDER BY 2

edited Feb 15 at 18:36

answered Feb 15 at 18:30

gzzz

3663 silver badges7 bronze badges

Collectives™ on Stack Overflow

Case When expression for Dates in SQL Query

4 Answers 4

9 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

9 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related