I'm new in sql and i try to change my where conditions based on a column value in my select from Orcale table, like bellow:

Select 
a 
,b
,c
,date_time
from t
where 
condition_1
or condition_2

so the condition_2 is i want to check if the date_time column is like 'date 00:00:00' so i will do

to_date(to_char(date_time,'yyyy-mm-dd'), 'yyyy-mm-dd') **>=** 
to_date(to_char(to_date('2022-01-01 11:11:59','yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd'),'yyyy-mm-dd')

if not then i will do this condition

date_time **>** to_date('2022-01-01 11:11:59','yyyy-mm-dd hh24:mi:ss')

i tried to do it with case when inside my where but can not find the good logical way.

Can anyone help me please! Thanks

CodePudding user response：

I'm having a hard time seeing wat you want to do with the condition example but based on the question if we want to switch depending on b:

Select 
a 
,b
,c
,date_time
from t
where 
  (b = 1 && condition_1) 
  or 
  (b = 2 && condition_2)

CodePudding user response：

Not quite clear what was the question about. We can split it to some parts.

1. Sample data

A	B	C	DATE_TIME
aaa	bbb	ccc	31.01.2022 11:59:59
ddd	eee	fff	28.02.2022 00:00:00
ggg	hhh	iii	24.12.2021 00:00:00
jjj	wkkk	lll	12.01.2022 22:01:10

1st quote from question "I want to check if the date_time column is like 'date 00:00:00'"
If we want to see which rows have time part of the DATE_TIME column = 00:00:00

Select  A, B, C, DATE_TIME, To_Char(DATE_TIME, 'hh24:mi:ss') "TIME_PART"
From    tbl
Where   DATE_TIME = TRUNC(DATE_TIME) 

A	B	C	DATE_TIME	TIME_PART
ddd	eee	fff	28-FEB-22	00:00:00
ggg	hhh	iii	24-DEC-21	00:00:00

2nd quote "if not then i will do this condition date_time > to_date('2022-01-01 11:11:59','yyyy-mm-dd hh24:mi:ss')"
let's try just this condition:

Select  A, B, C, DATE_TIME, To_Char(DATE_TIME, 'hh24:mi:ss') "TIME_PART"
From    tbl
Where   DATE_TIME > To_Date('2022-01-01 11:11:59','yyyy-mm-dd hh24:mi:ss')

A	B	C	DATE_TIME	TIME_PART
aaa	bbb	ccc	31-JAN-22	11:59:59
ddd	eee	fff	28-FEB-22	00:00:00
jjj	kkk	lll	12-JAN-22	22:01:10

It is clear that if we use OR between above two condition resul will be the same as no condition at all - all rows will be returned.
On the other hand, if we use AND between them...

Select  A, B, C, DATE_TIME, To_Char(DATE_TIME, 'hh24:mi:ss') "TIME_PART"
From    tbl
Where   DATE_TIME > To_Date('2022-01-01 11:11:59','yyyy-mm-dd hh24:mi:ss') AND 
        DATE_TIME = TRUNC(DATE_TIME) 

Then there will be just one row as a result:

A	B	C	DATE_TIME	TIME_PART
ddd	eee	fff	28-FEB-22	00:00:00

I don't know if this is of any help but that is all I could offer...