Get only date in oracle

Question

1. How do i strip everything else and get just the date:

I have the date in this format

2018-01-27T00:00:00

I should get in MM/DD/YYYY Format.

My Result should be 01/27/2018.

2. How do I remove all the special characters in $185,000.32 and just get Amount as 185000.32 Note: Amount is a field in table of Number Datatype.

Answer 1

1

a) if you have datetime as string and want output as string:

select to_char(to_date(substr('2018-01-27T00:00:00',1,10),'yyyy-mm-dd'),'MM/DD/YYYY')
from dual;

b) if you have variable/column of date type and want output as string:

select to_char( your_variable_or_column, 'MM/DD/YYYY' )
from dual;

2 if you have value as string

select 
    to_number( '$185,000.32', 
    'L9G999G999G999G999G999D99', 
    'NLS_NUMERIC_CHARACTERS=''.,'' NLS_CURRENCY=''$''' ) 
from dual;

or with regexp get result value as string

select REGEXP_REPLACE( '$185,000.32', '[^-0123456789\.]', '' ) 
from dual;