Date Time formats - Oracle 10g

SELECT CURRENT_DATE FROM dual; -- 13-Dec-2011 10:04:01

SELECT SYSDATE FROM dual; --  13-Dec-2011 10:04:34

SELECT SYSDATE - 10 FROM dual; --  3-Dec-2011 10:04:46 ..This means the current date - # of dates

SELECT add_months(SYSDATE, -22) FROM dual; -- 13-Feb-2010 10:05:38.This is adding # of months to the current
--date and if we want to substract any # of months then we have to put the '-' sign.

SELECT sessiontimezone, current_date FROM dual; -- +05:30     13-Dec-2011 10:07:27. First retrive the time zone.

SELECT EXTRACT(YEAR FROM DATE '2005-01-04') FROM dual;-- 2005                                 

SELECT EXTRACT(DAY FROM DATE '2005-01-04') FROM dual;- -- 4

SELECT EXTRACT(MONTH FROM DATE '2005-01-04') FROM dual;- -- 1

SELECT MONTHS_BETWEEN(SYSDATE+365, SYSDATE) FROM dual; -- 11.96774193548387096774193548387096774 This takes how many months
--between two months.

SELECT NEW_TIME(TO_DATE( '11-10-99 01:23:45','MM-DD-YY HH24:MI:SS'), 'AST', 'PST') "New Date and Time"FROM dual;
-- 9-Nov-2099 21:23:45

SELECT NEXT_DAY(SYSDATE, 'THU') FROM dual;-- 15-Dec-2011 10:29:44 . Thursday is the 15th dec when running this file
-- Othere options are SUN, MON, TUE, WED, THU, FRI, and SAT.

SELECT ROUND(TO_DATE ('27-OCT-01'),'YEAR') NEW_YEAR FROM dual; -- 1-Jan-2002
SELECT ROUND(TO_DATE ('27-MAY-01'),'YEAR') NEW_YEAR FROM dual; -- 1-Jan-2001 You can understand the different between the both examples

SELECT TO_DATE('01-JAN-2004') FROM dual; -- 1-Jan-2004

SELECT TO_DATE('01/02/2004', 'dd/MM/YY') FROM dual; -- 1-Feb-2004

SELECT TO_DATE('January 12, 2005, 11:03 A.M.', 'MONTH DD, YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE = American')
FROM dual; -- 12-Jan-2005 11:03:00

SELECT DUMP(SYSDATE) FROM dual; --Typ=13 Len=8: 219,7,12,13,10,48,43,0

SELECT VSIZE(SYSDATE+3) FROM dual; --7                         

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') FROM dual;  -- 13-DEC-2011 11:04:21

SELECT TO_CHAR(TRUNC(SYSDATE), 'DD-MON-YYYY HH:MI:SS') FROM dual;  -- 13-DEC-2011 12:00:00 ABOVE ONE ALSO COMPLIED AT THE SAME TIME.
--BUT THE TIME IS DIFFERENT OF BOTH AND THIS ONE'S TIME IS ALWAYS 12:00:00