Oracle: Some Important Date Queries

-- Day of Week
SQL> select to_char(sysdate,'D') from dual;
T
-
5


-- Day of Month
SQL> select to_char(sysdate,'DD') from dual;
TO
--
04


-- Day of Year
SQL> select to_char(sysdate,'DDD') from dual;
TO_
---
035


-- Name of WeekDay
SQL> select to_char(sysdate,'DAY') from dual;
TO_CHAR(SYSDATE,'DAY')
------------------------------------
THURSDAY


-- Abbreviated name of day
SQL> select to_char(sysdate,'DY') from dual;
TO_CHAR(SYSD
------------
THU


-- Week of the year
SQL> select to_char(sysdate,'IW') from dual;
TO
--
05


-- Week of Month
SQL> select to_char(sysdate,'W') from dual;
T
-
1


-- Month (0-12)
SQL> select to_char(sysdate,'MM') from dual;
TO
--
02


-- Abbreviated name of month
SQL> select to_char(sysdate,'MON') from dual;
TO_CHAR(SYSD
------------
FEB


-- Name of Month
SQL> select to_char(sysdate,'MONTH') from dual;
TO_CHAR(SYSDATE,'MONTH')
------------------------------------
FEBRUARY


-- Year Spelled
SQL> select to_char(sysdate,'YYYY') from dual;
TO_C
----
2011


-- Year
SQL> select to_char(sysdate,'YEAR') from dual;
TO_CHAR(SYSDATE,'YEAR')
------------------------------------------
TWENTY SIXTEEN


-- Quarter of Year
SQL> select to_char(sysdate,'Q') from dual;
T
-
1


-- Century
SQL> select to_char(sysdate,'CC') from dual;
TO
--
21


-- Hour in 0-12 format
SQL> select to_char(sysdate,'HH') from dual;
TO
--
08


-- Hour in 0-24 format
SQL> select to_char(sysdate,'HH24') from dual;
TO
--
08


-- Minutes
SQL> select to_char(sysdate,'MI') from dual;
TO
--
59


-- Seconds
SQL> select to_char(sysdate,'SS') from dual;
TO
--
27


-- Seconds past midnight
SQL> select to_char(sysdate,'SSSSS') from dual;
TO_CH
-----
32369


Related Posts:
- Why to Prefer Oracle Native Date Arithmetic over ANSI INTERVAL
- Playing With Truncate and Date
- Date Difference in Days, Months and Years
- Dates Difference in days, hours, minutes & seconds
- ORA-01830 date format picture ends before converting entire input string
- Oracle: Convert seconds to time - hh:mi:ss
- Oracle: convert time hh:mi:ss to seconds