Oracle Date Formatting Null date as 00/00/0000

Question

How can I format the Null Dates in my Oracle SQL to 00/00/0000. I am using NVL function but it doesn't recognize the 00/00/0000 as the date format.

Is there any Date Formatting available in Oracle SQL which formats null date to 00/00/0000

Why are you wanting to use 00/00/0000 instead of null? – Andrew Sehr Jan 03 '11 at 16:06 — Andrew Sehr, Jan 03 '11 at 16:06
very large or very small values can confuse the optimizer – Stephanie Page Jan 03 '11 at 20:14 — Stephanie Page, Jan 03 '11 at 20:14

score 20 · Accepted Answer · answered Jan 03 '11 at 16:11

20

Do the to_char first and wrap it in the NVL. For example,

select nvl(to_char(null, 'DD-MM-YYYY'), '00-00-0000') from dual

answered Jan 03 '11 at 16:11

DwB

37,124
11
56
82

Thanks, it worked for me. I didn't thought it was so easy to convert.!! :) – msbyuva Jan 03 '11 at 17:36

score 1 · Answer 2 · edited Aug 28 '19 at 08:24

1

Use this function to assign default values of null values of date:

SELECT id, NVL(start_date, to_date('20020101','YYYYMMDD')) FROM employee;

edited Aug 28 '19 at 08:24

Dharman

30,962
25
85
135

answered Aug 28 '19 at 05:05

Majid Hasan

11
1

score 0 · Answer 3 · edited Nov 29 '21 at 12:05

0

select * from mty_containers mt  where terminal_id=1
and (
    in_date>=decode(to_date('','dd/mm/yyyy'),'',mt.in_date,to_date('','dd/mm/yyyy'))
     or  (case when '' is null  then nvl(to_char(in_date,'dd/mm/yyyy'),'N') End ='N' )
     )
and 
   (
     in_date<=decode(to_date('','dd/mm/yyyy'),'',mt.in_date,to_date('','dd/mm/yyyy'))
     or  (case when '' is null  then nvl(to_char(in_date,'dd/mm/yyyy'),'N') End ='N' )
   )

edited Nov 29 '21 at 12:05

Suraj Rao

29,388
11
94
103

answered Nov 29 '21 at 12:04

Devendra Giri

1

Date fillter when from and to data is avialable then show only those records other wise all record will show – Devendra Giri Nov 29 '21 at 12:06
1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 29 '21 at 12:45

Oracle Date Formatting Null date as 00/00/0000

3 Answers3

Linked