3 Replies Latest reply on Jun 1, 2016 4:15 PM by BScarbrough

    Date/Time Settings Causing "Invalid Number" with TRUNC?

    BScarbrough

      Good Morning,

       

      I have a particular query (below) that executes successfully in certain sessions within SQL Developer, but not others. In the other sessions, an 'Invalid Number' error returns, unless one of the 'TRUNC' conditions is removed (only the TRUNC). Unfortunately, the only thing I can guess is the particular date/time settings associated with the user's SQL Developers session do not support the formats associated below, which I realize is a long shot. The current setting for the date format is 'DD-MON-YY HH:mm:ss AM'. Could the TO_CHAR '24HH' statement be causing the issue? I wanted to get some thoughts from some experts and determine if an NLS_DATE_FORMAT is really required here (especially since it executes elsewhere) or if there is a particular setting (date/time format) that can be utilized?

       

      SELECT

        TO_CHAR(DATE_1, 'YYYY-MM-DD') FIELD_1,

        TO_CHAR(DATE_1, 'HH24:MI:SS') FIELD_2

      FROM

        TABLE_1

      AND FIELD_1 >= TRUNC(SYSDATE)-1

      AND FIELD_1 < TRUNC(SYSDATE)

       

      Thanks in advance for any help!

        • 1. Re: Date/Time Settings Causing "Invalid Number" with TRUNC?

          1070f1fe-d537-47ef-aec3-00d8cc4d7d57 wrote:

           

          I have a particular query (below) that executes successfully in certain sessions within SQL Developer, but not others. In the other sessions, an 'Invalid Number' error returns, unless one of the 'TRUNC' conditions is removed (only the TRUNC). Unfortunately, the only thing I can guess is the particular date/time settings associated with the user's SQL Developers session do not support the formats associated below, which I realize is a long shot. The current setting for the date format is 'DD-MON-YY HH:mm:ss AM'. Could the TO_CHAR '24HH' statement be causing the issue? I wanted to get some thoughts from some experts and determine if an NLS_DATE_FORMAT is really required here (especially since it executes elsewhere) or if there is a particular setting (date/time format) that can be utilized?

           

          SELECT

            TO_CHAR(DATE_1, 'YYYY-MM-DD') FIELD_1,

            TO_CHAR(DATE_1, 'HH24:MI:SS') FIELD_2

          FROM

            TABLE_1

          AND FIELD_1 >= TRUNC(SYSDATE)-1

          AND FIELD_1 < TRUNC(SYSDATE)

          Thanks for posting the detailed info.

          The current setting for the date format is 'DD-MON-YY HH:mm:ss AM'.

          That isn't going to work. You are using 'mm' (which is MONTH) in the time field for minutes.

            TO_CHAR(DATE_1, 'HH24:MI:SS') FIELD_2

          See how that one uses the correct 'MI'?

          • 2. Re: Date/Time Settings Causing "Invalid Number" with TRUNC?
            BScarbrough

            Thanks for the correction rp. I was going by memory (was looking into another user's session), so I was wrong here.

            Using the assumption that the format is correct in the other user's session (with 'MI'), I am unable to comprehend the outcome here as it seems very strange. Delete 1 of the 2 'TRUNC' statements and the script works? In addition, using the same script in other environments works successfully. Have yet to run into this issue, granted I'm by no means an expert, but this isn't making any since

            • 3. Re: Date/Time Settings Causing "Invalid Number" with TRUNC?
              BScarbrough

              Thanks again rp for your insight. It ended up being due to a field discrepancy between the environments. There are some restrictions on my side to the visibility, hence I added an irrelevant post here. Hope I didn't cause too much of your time!

               

              Cheers.