4 Replies Latest reply: Dec 24, 2012 2:00 AM by ranit B RSS

    I want to reset my date to this format: 12/31/2012 11:59:59 PM .. please ad

    979256
      I want to reset my date to this format: 12/31/2012 11:59:59 PM - see code below:

      DECLARE
      v_latest_close DATE;
      BEGIN
      v_latest_close := TO_DATE ('12/31/2012 23:59:59 ','MM/DD/YYYY HH24:MI:SS');

      DBMS_OUTPUT.PUT_LINE('The new date format is : '|| v_latest_close);

      END;

      the code above displays only : 12/31/2012 instead of 12/31/2012 11:59:59 PM

      Please advise

      Hope
        • 1. Re: I want to reset my date to this format: 12/31/2012 11:59:59 PM .. please ad
          sb92075
          976253 wrote:
          I want to reset my date to this format: 12/31/2012 11:59:59 PM - see code below:

          DECLARE
          v_latest_close DATE;
          BEGIN
          v_latest_close := TO_DATE ('12/31/2012 23:59:59 ','MM/DD/YYYY HH24:MI:SS');

          DBMS_OUTPUT.PUT_LINE('The new date format is : '|| v_latest_close);

          END;

          the code above displays only : 12/31/2012 instead of 12/31/2012 11:59:59 PM

          Please advise

          Hope
          DBMS_OUTPUT.PUT_LINE('The new date format is : '|| TO_CHAR(v_latest_close,'MM/DD/YYYY HH:MI:SS PM');

          remove your other threads & mark them as answered!

          ALTER SESSION SET NLS_DATE_FORMAT='MM/DD/YYYY HH:MI:SS AM';
          • 2. Re: I want to reset my date to this format: 12/31/2012 11:59:59 PM .. please ad
            Hoek
            Or:
            SQL> declare
              2    v_latest_close date := date '2012-12-31' + 1-(1/24/60/60);
              3  begin
              4    dbms_output.put_line('The new date format is : '|| to_char( v_latest_close
              5                                                              , 'mm/dd/yyyy hh:mi:ss AM')
              6                        );
              7  end;
              8  /
            The new date format is : 12/31/2012 11:59:59 PM
            
            PL/SQL procedure successfully completed.
            See: http://www.sqlsnippets.com/en/topic-12338.html for more examples.
            • 3. Re: I want to reset my date to this format: 12/31/2012 11:59:59 PM .. please ad
              Frank Kulash
              Hi, Hope,

              Here's what's going on in a little more detail:
              976253 wrote:
              I want to reset my date to this format: 12/31/2012 11:59:59 PM - see code below:

              DECLARE
              v_latest_close DATE;
              BEGIN
              v_latest_close := TO_DATE ('12/31/2012 23:59:59 ','MM/DD/YYYY HH24:MI:SS');
              That's the correct way to convert a string (in this case, the literal '12/31/2012 23:59:59') into a DATE.
              Like all DATEs in Oracle, v_latest_close has no "format"; it is just a point in time, independent of how you might later choose to display it.
              DBMS_OUTPUT.PUT_LINE('The new date format is : '|| v_latest_close);
              The expressions before and after the || operator are both supposed to be strings.
              The expression before the || operator above is a string; it's a VARCHAR2 literal.
              The expression after the || operator is not a string, though; it's a DATE. Rather than raising an error, Oracle tries to convert it to the correct datatype for you, so it implicitly converts the DATE into a VARCHAR2, using the default format for DATEs in your session.
              END;

              the code above displays only : 12/31/2012
              Apparently, the default format in your session is 'MM/DD/YYYY'; that's how the DATE got converted to a string.
              instead of 12/31/2012 11:59:59 PM

              Please advise
              The first reply above shows how you can change the default format in your session, so that the implicit conversion will work the way you want. But watch out; implicit conversions are often a bad idea. Even if they work the way you want now, there are lots of ways they could stop working in the future.
              If you want a DATE displayed in a certain format, the best way is to use TO_CHAR to explicitly convert the DATE into a string in that format, like Hoek suggested.
              • 4. Re: I want to reset my date to this format: 12/31/2012 11:59:59 PM .. please ad
                ranit B
                Hi 976253,
                Try this...
                DECLARE
                v_latest_close DATE;
                BEGIN
                v_latest_close := TO_DATE ('12/31/2012 23:59:59 ','MM/DD/YYYY HH24:MI:SS');
                DBMS_OUTPUT.PUT_LINE('The new date format is : '|| to_char(v_latest_close,'mm/dd/yyyy hh12:mi:ss AM'));
                END;
                gives
                The new date format is : 12/31/2012 11:59:59 PM
                Here,
                'v_latest_close' had the string '12/31/2012 23:59:59' converted into DATE format (using TO_DATE).
                But, DATE even though internally is stored in different way, when displayed it gets formatted as per your NLS settings.

                So, just for sake of display, we used TO_CHAR with the desired Format String.

                NO need to change the variable from DATE to TIMESTAMP. (suggested by some user in the Duplicate post)
                TIMESTAMP is needed to hold 'extra' DATE attributes like - Fractional Seconds, TIMEZONE, etc.

                In this case, DATE will suffice. It internally stores the 'Time' details and only is matter of display using TO_CHAR.

                Hope that Helps,
                Ranit B.