Change DATE and also a TIMESTAMP to just a date as 'MM/DD/YYYY. Also, need dummy date when null 1

[kernal]

Change DATE and also a TIMESTAMP to just a date 'MM/DD/YYYY'

Table1:
Example in the date field: 2013-03-28 22:14:44. Need to have it as 03/28/2013

Table1 if null: 01/01/1900

Table 2:

Example in the timestamp field in table2: 2010-01-02 00:00:00.000000. Need to have it as 01/02/2010

Help is very appreciated!

 

[Andrzejek]

Check TRUNC() and NVL() functions in Oracle


---- Andy

There is a great need for a sarcasm font.

 

[Beilstwh]

A date or timestamp column will always contain both calendar and time information. To remove the time information will set it to midnight. Also the internal structure will always stay the same. If you want it to be displayed in a different mask then do the following

1* select to_char(sysdate,'mm/dd/yyyy hh:mi.ss am') from dual
SQL> /

TO_CHAR(SYSDATE,'MM/DD
----------------------
08/12/2019 10:01.10 am

SQL> edit
Wrote file afiedt.buf

1* select to_char(trunc(sysdate),'mm/dd/yyyy hh:mi.ss am') from dual
SQL> /

TO_CHAR(TRUNC(SYSDATE)
----------------------
08/12/2019 12:00.00 am

If the date column is null then

select nvl(date_col,to_date('01/01/1900','mm/dd/yyyy') from my_table

To display a date as month/day/year

SQL> select to_char(sysdate,'MM/DD/YYYY') FROM DUAL;

TO_CHAR(SY
----------
08/12/2019







Bill
Lead Application Developer
New York State, USA