Setting Oracle date field

[eluser]jules123[/eluser]
I have CI working with Oracle, thanks to all the good info on this forum.

Now I have a problem with a specific query that tries to set a date field in Oracle.

Here is the general code in my helper function (using Active records) -

$CI = & get_instance();
$oradb = $CI->load->database('oradb', TRUE);
$data_arr = array('username'=> 'abc','data_updated' => date('d-M-Y'));
$oradb->insert('users', $data_arr);

Now the above works fine, but if I try to change the date('d-M-Y') to anything more granular which includes hr/sec/mins, then I get the following error:
ORA-01830: date format picture ends before converting entire input string.

So it looks like since Oracle DATE type uses 'd-M-Y' as the default format, that's all it will accept as a string representation for date field...Would like to know if anyone has any thoughts on how I can set the field with the full date-timestamp, not just 'd-M-Y'.

Thanks in advance!

[eluser]pehaw[/eluser]
Hi

I'm guessing you have created your Oracle table with a datatype of DATE for column 'data_updated'?

If that is the case then you have a choice

1) Use Oracle TO_DATE function on your date variable. - ie TO_DATE(<string>, &lt;format mask&gt

eg to_date('21-jan-2009 12:30','dd-mm-YYYY hh:mi'))

2) Change the default Oracle date format to include minutes and seconds using NLS_DATE_FORMAT

3) Change your column to datatype TIMESTAMP rather than DATE.

[eluser]jules123[/eluser]
Excellent! Thanks so much!

I read somewhere that it is better to set the NLS_DATE_FORMAT at the client side. So I am executing query "alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS'" before my actual query and it's working fine

Thanks again.

[eluser]Unknown[/eluser]
You can do the "ALTER SESSION..." part in a hook, of course

[eluser]Gram3000[/eluser]
Hi,

I'm having a similar problem to the original poster. I have a CI application connected to an Oracle database. Queries are running fine except for when saving a date.

The Oracle database table has a date field of type 'Date', how do I pass a date to it? I've tried TO_DATE but I get an "Call to undefined function TO_DATE()" error.

Can anyone help?

Thank you.

[eluser]fszostak[/eluser]
Any advance? I have same problem :-(

[eluser]pehaw[/eluser]
Exactly what is your problem?

[eluser]fszostak[/eluser]
For example, I have a Oracle table with a field type DATE...

Then this table is mapped in a model like this code below.

Mymodel code:

Code:

class My_model extends Model {
    public $MYDATE;

    private function insert() {
        $this->db->insert("MY_TABLE", $this);
    }
}

Mycontroller code:

Code:

$mymodel = new My_model()
$mymodel->MYDATE = '06/08/2010 17:23:45';
$mymodel->insert();

I receive this error: ORA-01830: date format picture ends before converting entire input string.

If you set only date works okay. $mymodel->MYDATE = '06/08/2010';

In Oracle i need put TO_DATE function, but it is dont work in Active Record CI.... i have a lot of codes to make changes, because it is a migration from MySQL.

Thanks for attention.

[eluser]pehaw[/eluser]
Just follow what jules123 did above

[eluser]fszostak[/eluser]
Okay Okay! :-)

I used ALTER SESSION, works fine.
But i have a doubt, the ALTER SESSION need be called in all HTTP requests?

I tried make call only in first access, on login, apparently worked ... you have any suggestions to ensure the operation of all requests in my application.

Thanks.