NVL in BETWEEN date AND

Ok have a lot of records and mayebe one order number = 11233.

part of code which is not correct

 AND a.C_DATE BETWEEN TO_DATE (
                                   NVL ('&C_DATE_FROM', '1900-01-01'),
                                   'yyyy-mm-dd')
                            AND TO_DATE (
                                   NVL ('&C_DATE_TO', '2099-12-31'),
                                   'yyyy-mm-dd')

My question How Can I write a condition if a.C_DATE is null ???
Now I dont have a record when a.C_DATe is null

I wrote sth like this

 AND (a.C_DATE BETWEEN TO_DATE (
                                   NVL ('&C_DATE_FROM', '1900-01-01'),
                                   'yyyy-mm-dd')
                            AND TO_DATE (
                                   NVL ('&C_DATE_TO', '2099-12-31'),
                                   'yyyy-mm-dd') OR a.C_DATE IS NULL)

but I’m waiting for a long time….


2 Solutions
Solution

Give this a try:

AND (NVL(a.C_DATE, TO_DATE('1900-01-01','yyyy-mm-dd')) 
     BETWEEN TO_DATE (NVL('&C_DATE_FROM', '1900-01-01'), 'yyyy-mm-dd')
         AND TO_DATE (NVL('&C_DATE_TO',   '2099-12-31'), 'yyyy-mm-dd'))
Solution

maybe this way?

AND ( 
   a.C_DATE BETWEEN 
       TO_DATE('1900-01-01', 'yyyy-mm-dd') AND TO_DATE ('2099-12-31', 'yyyy-mm-dd') 
   OR a.C_DATE IS NULL
    )

why this larage date range?