Analysis of Oracle Time Functions

I. Operation of sysdate function 1 and sysdate
Addition

1. Select sysdate, add_months (sysdate, 12) from dual; -- add 1 year
2. Select sysdate, add_months (sysdate, 1) from dual; -- add January
3. Select sysdate, to_char (sysdate + 7,'Yyyy-mm-dd HH24: MI: ss') From dual; -- add 1 week
4. Select sysdate, to_char (sysdate + 1,'Yyyy-mm-dd HH24: MI: ss') From dual; -- add 1 day
5. Select sysdate, to_char (sysdate + 1/24,'Yyyy-mm-dd HH24: MI: ss') From dual; -- add 1 hour
6. Select sysdate, to_char (sysdate + 1/24/60,'Yyyy-mm-dd HH24: MI: ss') From dual; -- add 1 minute
7. Select sysdate, to_char (sysdate + 1/24/60/60,'Yyyy-mm-dd HH24: MI: ss') From dual; -- add 1 second

Subtraction

1. Select sysdate, add_months (sysdate,-12) from dual; -- minus 1 year
2. Select sysdate, add_months (sysdate,-1) from dual; -- minus January
3. Select sysdate, to_char (sysdate-7,'Yyyy-mm-dd HH24: MI: ss') From dual; -- minus 1 week
4. Select sysdate, to_char (sysdate-1,'Yyyy-mm-dd HH24: MI: ss') From dual; -- minus 1 day
5. Select sysdate, to_char (sysdate-1/24,'Yyyy-mm-dd HH24: MI: ss') From dual; -- minus 1 hour
6. Select sysdate, to_char (sysdate-1/24/60,'Yyyy-mm-dd HH24: MI: ss') From dual; -- minus 1 minute
7. Select sysdate, to_char (sysdate-1/24/60/60,'Yyyy-mm-dd HH24: MI: ss') From dual; -- minus 1 second

2. Common date formats

1. Date Format description
2. --------------------------------------------------------------------
3. YYYY/MM/DD -- year/month/day
4. YYYY-year (4 digits)
5. YYY -- year (3 digits)
6. YY-year (2 digits)
7. MM -- month
8. DD -- Date
9. D -- week
10. -- Sunday = 1 Monday = 2 Tuesday = 3
11. -- Wednesday = 4 Thursday = 5 Friday = 6 Saturday = 7
13. DDD-the day of the year
14. WW-week of the year
15. W -- week of January
16. YYYY/MM/DD HH24: MI: SS -- year/month/day (24 hours): minute: Second
17. YYYY/MM/dd hh: MI: SS -- year/month/day (12 hours): minute: Second
18. J -- Julian day, Bc 4712/01/01 is 1
19. RR/MM/DD-2000 question
20. -- 00-49 = lower world region; 50-99 = lower world region

Example

1. Select to_char (sysdate,'Yyyy/MM/dd') From dual; -- 2007/09/20
2. Select to_char (sysdate,'Yyyy') From dual; -- 2007
3. Select to_char (sysdate,'Yyy') From dual; -- 007
4. Select to_char (sysdate,'Yy') From dual; -- 07
5. Select to_char (sysdate,'Mm') From dual; -- 09
6. Select to_char (sysdate,'Dd') From dual; -- 20
7. Select to_char (sysdate,'D') From dual; -- 5
8. Select to_char (sysdate,'Ddd') From dual; -- 263
9. Select to_char (sysdate,'Ww') From dual; -- 38
10. Select to_char (sysdate,'W') From dual; -- 3
11. Select to_char (sysdate,'Yyyy/MM/DD HH24: MI: ss') From dual; -- 2007/09/20 15:24:13
12. Select to_char (sysdate,'Yyyy/MM/dd hh: MI: ss') From dual; -- 2007/09/20 03:25:23
13. Select to_char (sysdate,'J') From dual; -- 2454364
14. Select to_char (sysdate,'Rr/MM/dd') From dual; -- 07/09/20