7 Replies Latest reply: Aug 16, 2010 6:04 AM by EdStevens RSS

    Insert system date and time

    user10484841
      Hi,
      I'm trying to insert into the table's row the default time in which the data is added. I'm trying to do something like
      "INSERT INTO TEST (DATESS) VALUES (to_date(sysdate, 'yyyy/mm/dd:hh:mi:ssam'))"
      But this don't work. is there other more efficient way to insert the current time in which I add the new row to the table?


      regards
        • 1. Re: Insert system date and time
          amardeep.sidhu
          user10484841 wrote:
          Hi,
          I'm trying to insert into the table's row the default time in which the data is added. I'm trying to do something like
          "INSERT INTO TEST (DATESS) VALUES (to_date(sysdate, 'yyyy/mm/dd:hh:mi:ssam'))"
          But this don't work. is there other more efficient way to insert the current time in which I add the new row to the table?


          regards
          Simply use SYSDATE. Why are you TO_DATE'ing something that is already a date ?
          • 2. Re: Insert system date and time
            sb92075
            INSERT INTO TEST (DATESS) VALUES (sysdate);

            Above is all you need for INSERT!
            • 3. Re: Insert system date and time
              user10484841
              Ok, but is it possible to insert the sysdate into this format yyyy/mm/dd:hh:mi:ssam?
              • 4. Re: Insert system date and time
                rajeysh
                user10484841 wrote:
                Ok, but is it possible to insert the sysdate into this format yyyy/mm/dd:hh:mi:ssam?
                yes, you can
                refer the link:
                http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html
                • 5. Re: Insert system date and time
                  amardeep.sidhu
                  Re: How To Format Sysdate

                  Read the reply by SomeoneElse
                  You don't format date types. They are stored in an internal format that you and I generally don't care about.
                  When you say you want a date to appear in a certain format, you're taking a date type and converting it to a character string.
                  • 6. Re: Insert system date and time
                    sb92075
                    Ok, but is it possible to insert the sysdate into this format yyyy/mm/dd:hh:mi:ssam?
                    DATE datatype have NO format.
                    Format is a data presentation issue!

                    SELECT TO_CHAR(DATESS,'YYYY/MM/DD:HH:MI:SSAM') FROM TEST;
                    • 7. Re: Insert system date and time
                      EdStevens
                      user10484841 wrote:
                      Ok, but is it possible to insert the sysdate into this format yyyy/mm/dd:hh:mi:ssam?
                      Oracle always stores dates in its own internal format. to_date() is used to tell oracle how to parse a string of characters that we humans recognize as a date. If you just gave it a string of "05-04-09" how would it know what is what? That's what the to_date function is for. Conversly, when retrieving a DATE type you use to_char to tell oracle how you want the internal representation converted to a character string that you can use. Try the following:
                      select sysdate from dual;
                      select to_char(sysdate,'dd-mm-yy') from dual;
                      select to_char(sysdate,'mon-yyyy') from dual;
                      select to_char(sysdate,'dd-mon-yy') from dual;
                      select to_char(sysdate,'dd-Mon-yy hh:24:mi:ss') from dual;
                      alter session set nls_date_format='dd-mm-yy';
                      select sysdate from dual;
                      alter session set nls_date_format='mon-yyyy';
                      select sysdate from dual;
                      alter session set nls_date_format='dd-mon-yy';
                      select sysdate from dual;
                      alter session set nls_date_format='dd-Mon-yy hh24:mi:ss';
                      select sysdate from dual;