| Strange behavior with date functions [message #19432] |
Sun, 17 March 2002 17:31  |
Balaji Anandan
Messages: 2
Registered: March 2002
|
Junior Member |
|
|
Hi,
For comparison of a character value against a date value, I use to_date(char_value,'yyyy/mm/dd') since the char_value is in that format.
This works in select statement and does not work in update statement!!!
That too in one server it works and not in other. Can it be related to any specific date format settings in the server??
To explain more clearly,
select a,b from table1 where dt_from=to_date('2001/01/01','yyyy/mm/dd') works fine and returns a single record
whereas
update table1 set a='10' where dt_from=to_date('2001/01/01','yyyy/mm/dd') fails and returns 0 rows updated.
To add to this strange behavior, the statement
update table1 set a='10' where to_char(dt_from,'yyyy/mm/dd') ='2001/01/01' is successful and updates a record.
Has anyone faced this strange behavior and if so any solution??
Thanks in advance
Balaji
|
|
|
|
| Re: Strange behavior with date functions [message #19435 is a reply to message #19432] |
Sun, 17 March 2002 19:27  |
Srihari
Messages: 22
Registered: October 2000
|
Junior Member |
|
|
I have tested the queries given by U
select a,b from table1 where dt_from=to_date('2001/01/01','yyyy/mm/dd')
update table1 set a='10' where dt_from=to_date('2001/01/01','yyyy/mm/dd')
Both the queries worked fine.The rows are also updated by the above update query.
I have tested with Oracle 8i on Solaris platform
Iam anxious to know how the problem occured and on which platform
Srihari
|
|
|
|
| Re: Strange behavior with date functions [message #19448 is a reply to message #19432] |
Mon, 18 March 2002 21:15 |
Balaji Anandan
Messages: 2
Registered: March 2002
|
Junior Member |
|
|
Hi Hari,
The problem came in Oracle 8.1.7 on Windows 2000.
I had the same kind of statements working on some other similar servers (Oracle 8i on win 2k).
I feel it should be some parameter settings that is making the difference. Even if that is the case, I am really wondering how the to_date with that format works for select (it returns a row) whereas fails for update (updates 0 rows).
|
|
|
|
| Re: Strange behavior with date functions [message #19450 is a reply to message #19448] |
Tue, 19 March 2002 06:04 |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
|
Maybe the select gets it from the DB setting and the update gets it from the client setting and it doesn't seem to match. Take a look at NLS_DATE_FORMAT. It can be set in initSID.ora, environment variable, registry, SQL command window, login.sql, and glogin.sql. Lots of places to check.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
