3 Replies Latest reply: May 11, 2011 9:42 AM by Hoek RSS

    ORA-01849: hour must be between 1 and 12

    609621
      Dear all,



      SELECT SYSDATE FROM DUAL;

      returns

      11-MAY-2011 17:33:00



      SELECT * FROM stables AS OF TIMESTAMP TO_TIMESTAMP ('11-MAY-2011 14:30:00'); returns

      ORA-01849: hour must be between 1 and 12

      Any idea ?


      Kai
        • 1. Re: ORA-01849: hour must be between 1 and 12
          Frank Kulash
          Hi, Kai,
          KaiS wrote:
          Dear all,



          SELECT SYSDATE FROM DUAL;

          returns

          11-MAY-2011 17:33:00



          SELECT * FROM stables AS OF TIMESTAMP TO_TIMESTAMP ('11-MAY-2011 14:30:00'); returns

          ORA-01849: hour must be between 1 and 12
          Always pass (at least) 2 arguments to TO_TIMESTAMP, like this:
          TO_TIMESTAMP ( '11-MAY-2011 14:30:00'
                    , 'DD-MON-YYYY HH24:MI:SS;
                    )
          The second argument tells TO_TIMESTAMP what format you're using. If you don't pass it, then TO_TIMESTAMP will use some default format, which may not be what you want or expect.
          • 2. Re: ORA-01849: hour must be between 1 and 12
            Ganesh Srivatsav
            You need to specify proper format mask. Try the below.
            SELECT * FROM stables AS OF TIMESTAMP TO_TIMESTAMP ('11-MAY-2011 14:30:00','DD-MON-YYYY HH24:MI:SS') 
            G.
            • 3. Re: ORA-01849: hour must be between 1 and 12
              Hoek
              http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/functions213.htm#SQLRF06142
              http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements003.htm#SQLRF51062
              from a simple search through:
              http://www.oracle.com/pls/db112/homepage