4 Replies Latest reply: Nov 19, 2012 11:32 AM by malky RSS

    problems parsing a string of date format...

    malky
      Hi all, I'm having a hard time converting a date to a string.

      I was using to_char but after much research i thought using convert would be better so i do this:

      SELECT CONVERT(VARCHAR(8), "19 Nov 2012", 112) from dual;

      Where the specs for 112 is YYYYMMDD but i get the error "ORA-00936: missing expression", assume is cause Oracle wouldn't know how to read the string to convert it.

      How can i convert to string format YYYYMMDD a string of the form dd mmm yyy (as described above)?

      Thanks in advance,
        • 1. Re: problems parsing a string of date format...
          bencol
          SELECT TO_DATE('19 Nov 2012','dd Mon yyyy') 
          FROM   dual;
          
          SELECT TO_CHAR(TO_DATE('19 Nov 2012','dd Mon yyyy') ,'YYYYMMDD')
          FROM dual;
          CONVERT and double quotes looks like MS SQL Server to me
          • 2. Re: problems parsing a string of date format...
            JustinCave
            It appears that your research lead you to a SQL Server thread. SQL Server uses arbitrary numbers to represent date formats. Oracle does not-- the number 112 has no relevance to Oracle.

            In Oracle, you convert a date to a string using the TO_CHAR function
            SELECT to_char( date '2012-11-19', 'YYYYMMDD' )
              FROM dual
            Of course, that assumes that you are starting with a date (as I do with my date literal). If you are starting with a string that represents a date and you want to convert it to a string that represents that same date in a different format, you would need to use both a TO_DATE and a TO_CHAR to convert the original string to a date and the date to a different string
            SELECT to_char( to_date( '19 Nov 2012', 'DD Mon YYYY' ), 'YYYYMMDD' )
              FROM dual
            Justin
            • 3. Re: problems parsing a string of date format...
              Peter vd Zwan
              Hi,

              Try this:
              select
                '19 Nov 2012' txt_1
                ,to_char(TO_DATE('19 Nov 2012','dd mon yyyy'),'YYYYMMDD') txt_2
              FROM
                DUAL;
              
              
              TXT_1       TXT_2  
              ----------- --------
              19 Nov 2012 20121119 
              Note the mon in stewad of mmm in the date format string
              • 4. Re: problems parsing a string of date format...
                malky
                Thanks all, specially Justin, thanks for providing such a clear explenation.