'Substituting Date values with CASE statement

I have a set of records, which contains a Date1 column and a Date2 column. If a record has the Date1 value less than a particular date, then that value will be substituted with the value in the Date2 column for that record. The Date columns have type date. I'm trying to use a CASE statement but I get the new value for ALL records for some reason.

sqloracleoracle11goracle10goracle-sqldeveloper


Solution 1:[1]

What about nulls? 

select case when date1 < date '2016-01-01' then date2 else date1 end as your_date
  from your_table;

Solution 2:[2]

Try something like this;

CASE
    WHEN Date1 < @DateVariable
        THEN Date2
    ELSE Date1
END DateResult

Solution 3:[3]

Let's say you have that particular date in a variable such as :

DELCARE particularDate DATE
SET particularDate = '6/10/2016'

And you have your table such as :

CREATE TABLE Tab(ID INT, Name NVARCHAR(100), Date1 DATE, Date2 DATE)
INSERT INTO Tab VALUES(1, 'A', '10/10/2016', '11/10/2016')
INSERT INTO Tab VALUES(2, 'B', '9/10/2016', '12/16/2016')
INSERT INTO Tab VALUES(3, 'C', '1/10/2016', '11/10/2016')
INSERT INTO Tab VALUES(4, 'D', '2/10/2016', '4/10/2016')
INSERT INTO Tab VALUES(5, 'E', '4/10/2016', '2/10/2016')

The table will look like this :

ID  Name      Date1       Date2
1    A      2016-10-10  2016-11-10
2    B      2016-09-10  2016-12-16
3    C      2016-01-10  2016-11-10
4    D      2016-02-10  2016-04-10
5    E      2016-04-10  2016-02-10

Then you can use the following query : 

SELECT      t.ID,
            t.Name,
            CASE
                WHEN (t.Date1 < particularDate)
                THEN t.Date2
                ELSE t.Date1
            END AS GreaterDate
FROM        Tab t

This would give you the following result :

ID  Name    GreaterDate
1    A      2016-10-10
2    B      2016-09-10
3    C      2016-11-10
4    D      2016-04-10
5    E      2016-02-10

It would be useful if you specify what should occur in case Date1 and that particular date are equal. You can then explicitly define the result of this condition. You can do something like this :

SELECT      t.ID,
            t.Name,
            CASE
                WHEN (t.Date1 < particularDate) THEN t.Date2
                WHEN (t.Date1 > particularDate) THEN t.Date1
                ELSE t.Date1          --when date1 and particularDate are equal
            END AS GreaterDate
FROM        Tab t

Hope this helps!!!

Solution 4:[4]

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT CASE WHEN DATE1 < TO_DATE('1900/01/02 00:00:00', 'YYYY/MM/DD HH24:MI:SS') THEN DATE2 ELSE DATE1 END AS DATE_OF_INTEREST FROM TABLE;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ronnis
Solution 2 Rich Benner
Solution 3
Solution 4 TechGuy

Related Questions