Bug #5262

Error with oracle dates

Details

Submitted	2005-09-01 10:42 UTC
From	jbello at optaresolutions dot com
Status	No Feedback
Package	DB_DataObject
PHP Version	Irrelevant
OS	Irrelevant
Roadmaps	(Not assigned)

Comments

[2005-09-01 10:42 UTC] jbello at optaresolutions dot com

Description:
------------
To insert a date in oracle you need to use the 'to_date' sql function.
If you don't do it, oracle interprets it like a string and generates an error.

[2005-09-01 11:14 UTC] jbello at optaresolutions dot com

This is an example of the error.

>UPDATE USERS SET USERNAME = 'Ángel', IS_ADMIN = 'T' , >START_DATE = '2005-08-03 15:43:00'
> *
>
>ERROR at line 1:
>ORA-01861: literal does not match format string

In the query sentence, DataObject class writes dates like text:

'2005-08-03 15:43:00'

and in oracle you must write something like:

to_date('2005-08-03 15:43:00','YYYY-MM-DD HH24:MI:SS')

[2005-09-06 09:10 UTC] torsten dot roehr at gmx dot de

You can set an Oracle session parameter to avoid using to_date():

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'

Send this query to Oracle at the top of each page and DB_DataObjects cast date/time will work fine.