 | |
comparing a DATE column with "one minute ago", type warning?
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
comparing a DATE column with "one minute ago", type warning? -
10-08-2008
, 07:12 PM
#2
| |||
| |||
|
|
How should I be comparing a DATE column with "one minute ago"? I do this: select sysdate, sysdate- interval '1' minute from dual; and get just what I need, the time a minute ago: SYSDATE SYSDATE-INTERVAL'1'MINUTE ------------------------- ------------------------- 08-OCT-08 17:09:19 08-OCT-08 17:08:19 But in my PL/SQL, I get a warning on this code, where lastping is a DATE column: update mytable set status='silent' where lastping < (sysdate - interval '1' minute); ^ ^ col 28 col 36 Warning(16,28): PLW-07202: bind type would result in conversion away from column type Warning(16,36): PLW-07202: bind type would result in conversion away from column type Many TIA! Mark |
either using the correct datatype avoiding implicit conversion,
or realizing you could express a minute as a fraction of a data, as
the unit of a date column is a day.
Apart from that: you need to post a version (4 digits) and stop
posting this in the wrong forum, .misc.
The correct forum is .server.
--
Sybrand Bakker
Senior Oracle DBA
#3
| |||
| |||
|
|
How should I be comparing a DATE column with "one minute ago"? I do this: select sysdate, sysdate- interval '1' minute from dual; and get just what I need, the time a minute ago: SYSDATE SYSDATE-INTERVAL'1'MINUTE ------------------------- ------------------------- 08-OCT-08 17:09:19 08-OCT-08 17:08:19 But in my PL/SQL, I get a warning on this code, where lastping is a DATE column: update mytable set status='silent' where lastping < (sysdate - interval '1' minute); ^ ^ col 28 col 36 Warning(16,28): PLW-07202: bind type would result in conversion away from column type Warning(16,36): PLW-07202: bind type would result in conversion away from column type Many TIA! Mark |
either using the correct datatype avoiding implicit conversion,
or realizing you could express a minute as a fraction of a data, as
the unit of a date column is a day.
Apart from that: you need to post a version (4 digits) and stop
posting this in the wrong forum, .misc.
The correct forum is .server.
--
Sybrand Bakker
Senior Oracle DBA
#4
| |||
| |||
|
|
How should I be comparing a DATE column with "one minute ago"? I do this: select sysdate, sysdate- interval '1' minute from dual; and get just what I need, the time a minute ago: SYSDATE SYSDATE-INTERVAL'1'MINUTE ------------------------- ------------------------- 08-OCT-08 17:09:19 08-OCT-08 17:08:19 But in my PL/SQL, I get a warning on this code, where lastping is a DATE column: update mytable set status='silent' where lastping < (sysdate - interval '1' minute); ^ ^ col 28 col 36 Warning(16,28): PLW-07202: bind type would result in conversion away from column type Warning(16,36): PLW-07202: bind type would result in conversion away from column type Many TIA! Mark |
either using the correct datatype avoiding implicit conversion,
or realizing you could express a minute as a fraction of a data, as
the unit of a date column is a day.
Apart from that: you need to post a version (4 digits) and stop
posting this in the wrong forum, .misc.
The correct forum is .server.
--
Sybrand Bakker
Senior Oracle DBA
#5
| |||
| |||
|
|
How should I be comparing a DATE column with "one minute ago"? I do this: select sysdate, sysdate- interval '1' minute from dual; and get just what I need, the time a minute ago: SYSDATE SYSDATE-INTERVAL'1'MINUTE ------------------------- ------------------------- 08-OCT-08 17:09:19 08-OCT-08 17:08:19 But in my PL/SQL, I get a warning on this code, where lastping is a DATE column: update mytable set status='silent' where lastping < (sysdate - interval '1' minute); ^ ^ col 28 col 36 Warning(16,28): PLW-07202: bind type would result in conversion away from column type Warning(16,36): PLW-07202: bind type would result in conversion away from column type Many TIA! Mark |
either using the correct datatype avoiding implicit conversion,
or realizing you could express a minute as a fraction of a data, as
the unit of a date column is a day.
Apart from that: you need to post a version (4 digits) and stop
posting this in the wrong forum, .misc.
The correct forum is .server.
--
Sybrand Bakker
Senior Oracle DBA
#6
| |||
| |||
|
|
How should I be comparing a DATE column with "one minute ago"? I do this: select sysdate, sysdate- interval '1' minute from dual; and get just what I need, the time a minute ago: SYSDATE SYSDATE-INTERVAL'1'MINUTE ------------------------- ------------------------- 08-OCT-08 17:09:19 08-OCT-08 17:08:19 But in my PL/SQL, I get a warning on this code, where lastping is a DATE column: update mytable set status='silent' where lastping < (sysdate - interval '1' minute); ^ ^ col 28 col 36 Warning(16,28): PLW-07202: bind type would result in conversion away from column type Warning(16,36): PLW-07202: bind type would result in conversion away from column type |
According to
http://download.oracle.com/docs/cd/B...01.htm#g196492
the difference between DATE and INTERVAL is a DATE, so if lastping is
a DATE there should be no implicit conversion necessary.
Yours,
Laurenz Albe
#7
| |||
| |||
|
|
How should I be comparing a DATE column with "one minute ago"? I do this: select sysdate, sysdate- interval '1' minute from dual; and get just what I need, the time a minute ago: SYSDATE SYSDATE-INTERVAL'1'MINUTE ------------------------- ------------------------- 08-OCT-08 17:09:19 08-OCT-08 17:08:19 But in my PL/SQL, I get a warning on this code, where lastping is a DATE column: update mytable set status='silent' where lastping < (sysdate - interval '1' minute); ^ ^ col 28 col 36 Warning(16,28): PLW-07202: bind type would result in conversion away from column type Warning(16,36): PLW-07202: bind type would result in conversion away from column type |
According to
http://download.oracle.com/docs/cd/B...01.htm#g196492
the difference between DATE and INTERVAL is a DATE, so if lastping is
a DATE there should be no implicit conversion necessary.
Yours,
Laurenz Albe
#8
| |||
| |||
|
|
How should I be comparing a DATE column with "one minute ago"? I do this: select sysdate, sysdate- interval '1' minute from dual; and get just what I need, the time a minute ago: SYSDATE SYSDATE-INTERVAL'1'MINUTE ------------------------- ------------------------- 08-OCT-08 17:09:19 08-OCT-08 17:08:19 But in my PL/SQL, I get a warning on this code, where lastping is a DATE column: update mytable set status='silent' where lastping < (sysdate - interval '1' minute); ^ ^ col 28 col 36 Warning(16,28): PLW-07202: bind type would result in conversion away from column type Warning(16,36): PLW-07202: bind type would result in conversion away from column type |
According to
http://download.oracle.com/docs/cd/B...01.htm#g196492
the difference between DATE and INTERVAL is a DATE, so if lastping is
a DATE there should be no implicit conversion necessary.
Yours,
Laurenz Albe
#9
| |||
| |||
|
|
How should I be comparing a DATE column with "one minute ago"? I do this: select sysdate, sysdate- interval '1' minute from dual; and get just what I need, the time a minute ago: SYSDATE SYSDATE-INTERVAL'1'MINUTE ------------------------- ------------------------- 08-OCT-08 17:09:19 08-OCT-08 17:08:19 But in my PL/SQL, I get a warning on this code, where lastping is a DATE column: update mytable set status='silent' where lastping < (sysdate - interval '1' minute); ^ ^ col 28 col 36 Warning(16,28): PLW-07202: bind type would result in conversion away from column type Warning(16,36): PLW-07202: bind type would result in conversion away from column type |
According to
http://download.oracle.com/docs/cd/B...01.htm#g196492
the difference between DATE and INTERVAL is a DATE, so if lastping is
a DATE there should be no implicit conversion necessary.
Yours,
Laurenz Albe
#10
| |||
| |||
|
|
How should I be comparing a DATE column with "one minute ago"? I do this: * * select sysdate, sysdate- interval '1' minute from dual; and get just what I need, the time a minute ago: * * SYSDATE * * * * * * * * * SYSDATE-INTERVAL'1'MINUTE * * ------------------------- ------------------------- * * 08-OCT-08 17:09:19 * * * *08-OCT-08 17:08:19 * * * * But in my PL/SQL, I get a warning on this code, where lastping is a DATE column: * * * * update mytable * * * * * *set status='silent' * * * * *where lastping < (sysdate - interval '1' minute); * * * * * * * * * * * * * *^ * * * ^ * * * * * * * * * * * col 28 * * col 36 Warning(16,28): PLW-07202: bind type would result in conversion * * * * * * * * away from column type Warning(16,36): PLW-07202: bind type would result in conversion * * * * * * * * away from column type Many TIA! Mark -- Mark Harrison Pixar Animation Studios |
update mytable
set status='silent'
where lastping < (sysdate - 1/1440);
and achieve the same result.
David Fitzjarrell
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.