Update a datetime field with 1 minute older time in pro *C

Question

We have implemented the following query in pro *C with Oracle database, which works properly.

update anp
set lssn = :ssn,
ltd = :td;

Here ltd is a datetime field and td is a datetime variable. Now we want to implement this query in such a way that ltd will be updated with 1 min lesser time from td. Example: If td has a value 2014-01-02 14:44:03, ltd should be updated with 2014-01-02 14:43:03.

I have tried many things like:

update anp
set lssn = :ssn,
ltd = :td -(1/1440);

update anp
set lssn = :ssn,
ltd = :td + interval '-60'  second;

And many things but I failed to achieve it. Please help me.

Your statements seem correct and should work. How exactly do they fail? — Thorsten Kettner, Jan 09 '14 at 07:02
@MaheswaranRavisankar-`EXEC SQL BEGIN DECLARE SECTION; dtime_t td; EXEC SQL END DECLARE SECTION;` — , Jan 09 '14 at 07:07
@MaheswaranRavisankar - first one gives PL/SQL found semantic errors during compilation — , Jan 09 '14 at 07:10
You'll need to convert `:td` to an actual `date` first. How is the `dtime_t` type defined - why do you have your own type for dates at all? (You know `date` includes times down to second precision, right, and `timestamp` exists if you need greater precisin than that?) — Alex Poole, Jan 09 '14 at 07:49
you can see the `.c` and check how this statement is framed. Looks like the problem with `dtime_t`. `30081` error is because, of the date unrecognised or invalid. — Maheswaran Ravisankar, Jan 09 '14 at 07:49
@AlexPoole- Can you tell me the declaretion of TD as date datatype — , Jan 09 '14 at 09:26
Sorry, it's been a while, I was thinking the declare section had Oracle types but of course it doesn't. What is `dtime_t` - a structure, a string, a `varchar` with `.arr` set to `2014-01-02 14:44:03`? If your first query works then it must be something Oracle can recognise but it looks like you're doing an implicit conversion. — Alex Poole, Jan 09 '14 at 10:46

Alex Poole · Accepted Answer · 2014-01-09T10:55:49.177

It looks like td is a string representation of a date, so dtime_t seems to be a structure - possibly a VARCHAR. If so, you're doing an implicit date conversion at the moment, which isn't a good idea anyway as it relies on the NLS settings of the client application; which are maybe controlled in a Pro*C app more than in other scenarios, but still a potential failure point. But in this case you're trying to subtract the interval from the string before that implicit conversion has happened; hence the ORA-30081 error.

Changing that to an explicit conversion would allow the interval arithmetic:

update anp
set lssn = :ssn,
ltd = to_date(:td, 'YYYY-MM-DD HH24:MI:SS') - interval '1' minute;

A simple demonstration:

select '2014-01-02 14:44:03' - interval '1' minute from dual;

SQL Error: ORA-30081: invalid data type for datetime/interval arithmetic
30081. 00000 -  "invalid data type for datetime/interval arithmetic"
*Cause:    The data types of the operands are not valid for datetime/interval
           arithmetic.

Versus:

select to_date('2014-01-02 14:44:03', 'YYYY-MM-DD HH24:MI:SS')
  - interval '1' minute from dual;

TO_DATE('2014-01-0214:44:03','YYYY-MM-DDHH24:MI:SS')-INTERVAL'1'MINUTE
----------------------------------------------------------------------
2014-01-02 14:43:03

@1053588 - I'm not familiar with informix, but it [has a `to_date` function too](http://pic.dhe.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqls.doc%2Fids_sqs_1543.htm); so I guess same mechanism, different format model? — Alex Poole, Jan 12 '14 at 09:28

Update a datetime field with 1 minute older time in pro *C

1 Answers1