There are three datatypes you can use to work with dates and times: DECLARE l_today_date DATE := SYSDATE; l_today_timestamp TIMESTAMP := SYSTIMESTAMP; l_today_timetzone TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP; l_interval1 INTERVAL YEAR (4) TO MONTH := '2011-11'; l_interval2 INTERVAL DAY (2) TO SECOND := '15 '; BEGIN null; END; Working with intervals and time stamps with time zones can be very complicated; relatively few developers will need these more advanced features.This article focuses on the core DATE and TIMESTAMP types, along with the most commonly used built-in functions. With such an abundance of riches, how do you decide which of these date-and-time datatypes to use?He is also the first recipient of ODTUG's Lifetime Achievement Award (2009).Both datetime and interval data types consist of fields.For full explanations of both of these answers, visit plsqlchallenge.com, register or log in, and click the Closed/Taken tab in Play a Quiz, or go to bit.ly/r1Swv P.Steven Feuerstein is Oracle Corporation's Developer Advocate for PL/SQL, and an expert on the Oracle PL/SQL language, having written ten books on PL/SQL, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices (all published by O' Reilly Media).
It is made of information on century, year, month, date, hour, minute, and second.
Provide the string and Oracle Database returns a date or a time stamp, using the default format mask for the session: You should not assume that the literal value you provide in your call to TO_DATE matches the default format. Instead, always provide a format mask when converting strings to dates, as in Date truncation.