Menu
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
674
The simplest way to put NULL into any column, regardless of the datatype, is: INSERT INTO emp (hiredate) VALUES (NULL); Don't use single-quotes around NULL. Putting it in single-quotes makes it a 4-character string value.
Hi, YYYY gives the current year as 4 digits. RRRR format means 2-digit years in the range 00 to 49 are assumed to be in the current century (ie have the same first two digits as the current year), and years given as 50 through 99 are assumed to be in the previous century. 0 · Share on TwitterShare on Facebook.
Use TO_CHAR to display it in any format you like. For example: SELECT TO_CHAR ( TO_DATE (date_value, 'yyyy-mm-dd') , 'mm/dd/yyyy' ) FROM table_x; Things are much easier if you store dates in DATE columns.
So, we can use the NVL function. We first need to work out what value we want to display. This could be a static date (i.e., 31-DEC-9999), for example. As you can see, the NVL function can be used to translate a NULL date value to something else.
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 dualUse this function to assign default values of null values of date:
SELECT id, NVL(start_date, to_date('20020101','YYYYMMDD')) FROM employee;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
