Sunday, April 11, 2010

How to add a day/hour/minute/second to a date value

How to add a day/hour/minute/second to a date value
====================================================


SELECT SYSDATE "Now",
SYSDATE + 1 "Tomorow/ next day",
SYSDATE + 7 "Seven days from now",
SYSDATE + 1 / 24 "One hour from now",
SYSDATE + 3 / 24 "Three hours from now",
SYSDATE + 1 / 48 "A half hour from now",
SYSDATE + 10 / 1440 "10 minutes from now",
SYSDATE + 30 / 86400 "30 seconds from now ",
TRUNC (SYSDATE + 1) "Tomorrow at 12 midnight",
TRUNC (SYSDATE + 1) + 8 / 24 "Tomorrow at 8 AM",
NEXT_DAY (TRUNC (SYSDATE), 'MONDAY')+ 12 / 24 "Next Monday at 12:00 noon",
TRUNC (LAST_DAY (SYSDATE) + 1) "1 day of month at 12 midnight",
TRUNC (LEAST (NEXT_DAY (SYSDATE, 'MONDAY'),
NEXT_DAY (SYSDATE, 'WEDNESDAY'),
NEXT_DAY (SYSDATE, 'FRIDAY')))+9/24 "Next Mon,Wed or Friday at 9am"
FROM DUAL ;

Output:=
=========

Now Tomorow/ next day Seven days from now One hour from now Three hours from now
04-11-2010 13:37:03 04-12-2010 13:37:03 04-18-2010 13:37:03 04-11-2010 14:37:03 04-11-2010 16:37:03
A half hour from now 10 minutes from now 30 seconds from now Tomorrow at 12 midnight Tomorrow at 8 AM
04-11-2010 14:07:03 04-11-2010 13:47:03 04-11-2010 13:37:33 04-12-2010 00:00:00 04-12-2010 08:00:00
Next Monday at 12:00 noon 1 day of month at 12 midnight Next Mon,Wed or Friday at 9am

04-12-2010 12:00:00 05-01-2010 00:00:00 04-12-2010 09:00:00



No comments: