DATE Manipulation

Question

I would appreciate any pointers with this, I'm trying to ultimately get the Day of the week for each date. Unfortunately my DATESTRG in format 02-JUL-13 is ending up as 13/07/0002 rather than 02-07-2013 ( European date format ), and I get an error when I try to get the Day of Week DOW. Thank you.

WITH DATEDATE AS 
(
    SELECT 
    SUBSTR ( SRT.CREATED_DATE,1,10) AS DATESTRG
    FROM SMS.REVIEW_TEXT SRT
  )
SELECT 
    DATESTRG,
    TO_DATE ( DATESTRG, 'YYYY-MM-DD' )
    TO_CHAR ( DATE DATESTRG, 'DY') AS DOW
FROM DATEDATE 

Answer 1

Try this:

 WITH DATEDATE AS 
    (
        SELECT 
        SUBSTR ( '2017-09-08',1,10) AS DATESTRG
        FROM dual
      )

 SELECT      
        TO_CHAR (to_date(DATESTRG,'YYYY-MM-DD'), 'DY') AS DOW
    FROM DATEDATE