How to extract date part from date time?

Asked By jena.smith 0 points N/A Posted on - 08/16/2011

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?

• Status: Open
• Question Views: 1171
• Answer Count: 1
• Vote Up 0 Vote Down

• Answer Accepted: No
• Question Category: Oracle Database

Answered By hophi168 0 points N/A #115617

How to extract date part from date time?

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.

 

About hophi168

Questions
0

Answers
1

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 08/18/2011
• Question Category: Oracle Database