Oracle: how to add minutes to a timestamp?

oracle date timestamp date-arithmetic
Sajee picture Sajee · Nov 2, 2008 · Viewed 253.3k times · Source

I need to add 30 minutes to values in a Oracle date column. I do this in my SELECT statement by specifying

to_char(date_and_time + (.000694 * 31)

which works fine most of the time. But not when the time is on the AM/PM border. For example, adding 30 minutes to 12:30 [which is PM] returns 1:00 which is AM. The answer I expect is 13:00. What's the correct way to do this?

Answer

Justin Cave picture Justin Cave · Nov 2, 2008

In addition to being able to add a number of days to a date, you can use interval data types assuming you are on Oracle 9i or later, which can be somewhat easier to read,

SQL> ed
Wrote file afiedt.buf
SELECT sysdate, sysdate + interval '30' minute FROM dual
SQL> /

SYSDATE              SYSDATE+INTERVAL'30'
-------------------- --------------------
02-NOV-2008 16:21:40 02-NOV-2008 16:51:40

Related questions

Updating a date in Oracle SQL table

I am trying to update a date in a SQL table. I am using Peoplesoft Oracle. When I run this query: Select ASOFDATE from PASOFDATE; I get 4/16/2012 I tried running this query UPDATE PASOFDATE SET ASOFDATE = '11/21/2012'; but it …

Read More
How to get the week day name from a date?

Given 03/09/1982 how can we say it is which week day. In this case it will be Tue. Is it possible to get in a single query?

Read More
Check if current date is between two dates Oracle SQL

I would like to select 1 if current date falls between 2 dates through Oracle SQL. I wrote an SQL after reading through other questions. https://stackoverflow.com/questions/2369222/oracle-date-between-query https://stackoverflow.com/questions/2399753/select-from-table-by-knowing-only-date-without-time-oracle But it returned only null. sysdate is …

Read More