How to convert date string to date in oracle

Question

Screenshot of query executed The date string in 'Sat Oct 29 10:47:50 CDT 2016' has to be converted to date/timestamp format in oracle query. I need to extract only date and time from it. I tried using to to_date() but didn't work. Can one someone please help.

Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0" *Cause: Illegal year entered *Action: Input year in the specified range

Please post the to_date() syntax you used. Which format did you use? docs.oracle.com/cd/B19306_01/server.102/b14200/… — Nick.Mc
– Nick.Mc, Commented Jan 31, 2017 at 6:14
Please show in the question how did you use to_date function.? — krokodilko
– krokodilko, Commented Jan 31, 2017 at 6:15
@Wernfried Please give a solution then if you have one. I have never even used Oracle hence I could not test my answer. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jan 31, 2017 at 7:49
Order_date has the string format of timestamp of order received as mentioned above and query is -- select to_date(order_date ,'YYYY-MM-DD HH24:MI:SS') from table_name where rownum<=3; — Manasa Salla
– Manasa Salla, Commented Jan 31, 2017 at 9:23

Wernfried Domscheit · Accepted Answer · 2017-01-31 07:49:30Z

1

Try this one:

SELECT TO_DATE(
   REGEXP_REPLACE('Sat Oct 29 10:47:50 CDT 2016', '^\w+ (\w+ \d+ \d{2}:\d{2}:\d{2}).+(\d{4})', '\1 \2'), 
      'Mon DD HH24:MI:SS YYYY')
FROM DUAL

Data type DATE or TIMESTAMP does not support any time zone information, you have to remove this part before you can do the conversion.

answered Jan 31, 2017 at 7:49

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Manasa Salla Over a year ago

This extracts only date from it, we need time also from it.. the DB column has date stored as "Sat Oct 29 10:47:50 CDT 2016" in string format and we need to pull the records between 15min/30min interval by querying on the string colum

Wernfried Domscheit Over a year ago

No, it also reads the time as by HH24:MI:SS. Check NLS_DATE_FORMAT setting of your current session.

Manasa Salla Over a year ago

Yeah.."HH24:MI:SS" this reads the time, but somehow the output didn't return the time, it only shows . And the NLS_DATE_FORMAT is "DD-MON-RR" am not aware of how this effects the query output? query screenshot [i.sstatic.net/YZOz2.png]

Wernfried Domscheit Over a year ago

NLS_DATE_FORMAT defines the default output format of DATE values. Change it (e.g. to yyyy-mm-dd hh24:mi:ss) or define the output format explicitly by TO_CHAR(TO_DATE(REGEXP_REPLACE(...., 'yyyy-mm-dd hh24:mi:ss')

Collectives™ on Stack Overflow

How to convert date string to date in oracle

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related