Is there any way to use CASE in WHERE clause in ORACLE?-CodePudding

I have the following data in DB which supposed to be a date but in number format:

date_col:

20200130.95656
20200910.85213
0
20220101.55412
0
0
20220730.85626

Now I need to format the dates to mm/dd/yyyy so I used the following command in oracle:

to_char(to_date(trunc(date_col),'yyyymmdd'),'mm/dd/yyyy')

to output it like this 07/30/22. But the problem I still need to get the 0 values in the date_col and at the same time to only get the dates between 09/10/2020 to 07/30/2022 so I was wondering if there is a way to do this in oracle?

I can filter the dates by using the following code:

SELECT char(to_date(trunc(date_col),'yyyymmdd'),'mm/dd/yyyy')
FROM table1 WHERE
date_col != 0
AND char(to_date(trunc(date_col),'yyyymmdd'),'mm/dd/yyyy') BETWEEN '09/10/2020' AND '07/30/2022';

but I need to get the 0 at the same time. I'm thinking on using CASE on the WHERE clause but don't know how. I googled but doesn't seems to answer my question. I'm only used to using CASE on the SELECT and not on WHERE so I'm wondering if there is a trick on using it on WHERE. Any help and tips would be appreciated!

Update: Output should be:

data_col
________________
 - 09/10/2020
 - 0
 - 01/01/2022
 - 0
 - 0
 - 07/30/2022

Date is on number because of vendor who created the formatting of the date..

CodePudding user response：

Before we get into this, there are a few things I need to point out here. I know you may not have much influence on the schema design, but you need to understand you are working with a BROKEN SCHEMA.

Building on this, it bears repeating internationalization/cultural issues mean converting these numbers to strings and then to dates is about the slowest and most error-prone way possible to accomplish the conversion to date values.

Finally, the BETWEEN '09/10/2020' AND '07/30/2022' comparison is horribly broken. The left-hand side casts back to char and so you are doing string comparisons instead of date comparisons. There is no value that will ever satisfy this condition, because when evaluating the boundary strings the 0s are equal, the 9 is already greater than the 7, and that's as far as you'll ever get.

That out of the way, you can use CASE in a WHERE clause. However, remember that CASE is not like an if() statement. You don't use it to select code branches. It is an expression... it produces a value. Therefore, to use CASE with a WHERE clause, you want something to compare with that value result. For example: WHERE 0 = CASE WHEN SomeColumn=1 THEN 0 ELSE 1 END

But we don't need CASE here. This will do the job:

SELECT char(to_date(trunc(date_col),'yyyymmdd'),'mm/dd/yyyy')
FROM table1 
WHERE date_col = 0 OR date_col BETWEEN 20200910.0 AND 20220730.99999

This will perform much better, not only because it avoids the extra conversions, but in leaving the column values intact for the comparison it also preserves the ability to use indexes with the column, which cuts to the core of database performance.

CodePudding user response：

You could use this. If your date_col have no decimal part you could use BETWEEN instead

SELECT TO_CHAR(TO_DATE(TRUNC(date_col),'yyyymmdd'),'mm/dd/yyyy')
FROM table1 
WHERE date_col = 0
    OR (date_col >= 20200910 AND date_col < 20200730);