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

5 comments:

  1. how we can learn oracle fore staring give same nots or video thnx raazhoon@hotmail.com

    ReplyDelete
  2. you can get the pdf on oracle site itself for free

    ReplyDelete
  3. select to_char(sysdate,'CC') from dual to get the century information from a date......

    ReplyDelete
  4. I read this piece of writing completely regarding the resemblance of most recent and earlier technologies, it's
    remarkable article.

    ReplyDelete
  5. Hi bro those are very helpful to started guys. thank you bro..keep it up

    ReplyDelete