oracle, sql date function

Question

I want to update my date column, need to add 35 seconds. I used this query:

UPDATE temp_table SET dpdate = dpdate + 35 / 86400 ;

Here is the problem:

EXISTING             AFTER Update
-------------------- --------------------
02-JUN-2017 02:06:13 02-JUN-2017 02:06:48
02-JUN-2017 02:06:14 02-JUN-2017 02:06:49
19-MAY-2017 02:05:47 19-MAY-2017 02:05:22

Note: I ran the above query, and all results below are after running that update query.

In the first two lines, it worked fine, but in the last one, it should come to 02:06:22 but it is 02:05:22.

When I do order by date, 19-MAY-2017 02:05:47 comes first and then 19-MAY-2017 02:05:22.

Not sure why the minute field is not incremented, and how in order by, 19-MAY-2017 02:05:47 comes first.

Few examples of order by, the first four are correct, but you can see the problem with last 2:

A    01-DEC-2014 12:12:00
C    01-DEC-2014 12:12:35

A    01-JUL-2016 05:07:33
C    01-JUL-2016 05:07:08

A    29-JAN-2016 04:01:08
C    29-JAN-2016 04:01:43

A    26-FEB-2016 04:02:09
C    26-FEB-2016 04:02:44

A    11-MAR-2016 03:03:13
C    11-MAR-2016 03:03:48

A    08-APR-2016 04:04:35
C    08-APR-2016 04:04:10

My question: Why the minute field is not updated?

Boneist · Accepted Answer · 2017-09-05 09:09:39Z

4

My bet is you're using: to_char(dpdate, 'dd-MON-yyyy hh24mmss') to display the date (either that, or your NLS_DATE_FORMAT parameter has been changed with that format mask) - note the mm in the time section. mm is used to get the month number, whereas you probably need mi to get the minutes.

E.g.:

WITH sample_data AS (SELECT to_date('02-JUN-2017 02:06:13', 'dd-MON-yyyy hh24:mi:ss') dpdate FROM dual UNION ALL
                     SELECT to_date('02-JUN-2017 02:06:14', 'dd-MON-yyyy hh24:mi:ss') dpdate FROM dual UNION ALL
                     SELECT to_date('19-MAY-2017 02:05:47', 'dd-MON-yyyy hh24:mi:ss') dpdate FROM dual)
SELECT to_char(dpdate, 'dd/mm/yyyy hh24:mi:ss') dpdate,
       to_char(dpdate + 35/(24*60*60), 'dd/mm/yyyy hh24:mm:ss') incorrect_new_dpdate_display,
       to_char(dpdate + 35/(24*60*60), 'dd/mm/yyyy hh24:mi:ss') correct_new_dpdate_display
FROM   sample_data;

DPDATE              INCORRECT_NEW_DPDATE_DISPLAY CORRECT_NEW_DPDATE_DISPLAY
------------------- ---------------------------- --------------------------
02/06/2017 02:06:13 02/06/2017 02:06:48          02/06/2017 02:06:48
02/06/2017 02:06:14 02/06/2017 02:06:49          02/06/2017 02:06:49
19/05/2017 02:05:47 19/05/2017 02:05:22          19/05/2017 02:06:22

answered Sep 5, 2017 at 9:09

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jaya Over a year ago

Thank you, this works! :) I was using MM instead of MI.

Piotr Rogowski · Accepted Answer · 2017-09-05 09:05:00Z

0

Are you sure that your operation is fine ?

Check this query, it's work fine, minutes are changed:

select current_date,current_date + 35 / 86400 from dual

answered Sep 5, 2017 at 9:05

Piotr Rogowski

3,90021 silver badges24 bronze badges

Comments

Peter Abolins · Accepted Answer · 2017-09-05 09:20:02Z

0

You could use INTERVAL

UPDATE temp_table SET dpdate = dpdate + INTERVAL '35' SECOND;

On Oracle 11g this works fine, and updates the minutes as well. It is also easier to read, as compared to an arithmetic expression.

edited Sep 5, 2017 at 9:20

answered Sep 5, 2017 at 9:10

Peter Abolins

1,5391 gold badge11 silver badges18 bronze badges

Collectives™ on Stack Overflow

oracle, sql date function

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related