I want to take only the date part of passed date time string.
Example:
If I pass '8/16/2011 12:47:01.698187 AM -4.00 AM' . I only require '8/16/2011' from the following query:
select trunc(''8/16/2011 12:47:01.698187 AM -4.00 AM') from dual;
But it is throwing an error: ORA 01722- invalid number .
Can you please correct the query?
- Login or Signup Now to post comments
Hi Jena.smith,
The error occurs as Oracle cannot understand your date string.
For the date string provided, you can use substr function, in Oracle to cut the first 10 characters of “ 8/16/2011 12:47:01.698187 AM -4.00 AM”. You can follow the SQL below:
SELECT rtrim(substr('8/16/2011 12:47:01.698187 AM -4.00 AM',1,10)) AS DATEEXTRACT FROM dual;
In case you want to change the cut string to formular DATE, you can follow the next SQL as below:
Using To_DATE: SELECT TO_DATE(substr('8/16/2011 12:47:01.698187 AM -4.00 AM',1,10), 'MM/DD/YYYY') AS DATEEXTRACT FROM dual;
In case you want to format the cut stringinto your own way, you can follow the SQL as below:
Using TO_CHAR: SELECT TO_CHAR(TO_DATE(substr('8/16/2011 12:47:01.698187 AM -4.00 AM',1,10), 'MM/DD/YYYY'), 'MM-DD-YYYY') AS DATEEXTRACT FROM dual;
'MM-DD-YY'" this string can be changed in your own way. For example: DD/MM/YYYY
Hope this helps.