2 Replies Latest reply on Apr 21, 2013 6:59 PM by Frank Kulash

    DATE and TIMESTAMP

    EV259
      All,

      I am converting some sql server scripts to oracle scripts. I have come across lot many times the DATETIME data type was used in the sql server scripts.

      http://docs.oracle.com/cd/E10405_01/doc/appdev.120/e10379/ss_oracle_compared.htm

      In the above link I have came across DATETIME(sql server) is equal to the DATE / TIMESTAMP(Oracle) in Oracle.

      Which one is best to use among the DATE and TIMESTAMP, performance wise. Please give me some suggestions.

      Thanks,
        • 1. Re: DATE and TIMESTAMP
          jeneesh
          Performance has no role to play in selecting which data type to use.

          DATE in oracle can store the date value and the time value up to seconds.

          Apart from this, TIMESTAMP can store fractional seconds also. TIMESTAMP WITh TIMEZONE can store zone data also..

          So, you have to select datatype based on what data you want to store.. If you are interested only in date value (plus time upto seconds), there is NO NEED to use TIMESTAMP data type..
          1 person found this helpful
          • 2. Re: DATE and TIMESTAMP
            Frank Kulash
            Hi,
            Use DATE when you have a choice.
            I'm sure DATEs are more efficient than TIMSTAMPs (though I don't have any solid evidence). Also, DATEs require less storage space, and there are more functions available for manipulating DATEs. (These funtions usually work on TIMESTAMPs also, but they start by converting the TIMESTAMP to a DATE.)