Hi,
My database server is Oracle. I have some fields with datetime datatype. I could retrieve or update those fields as long as I need only date. But I need to update current datewithtime. Whatever format I use the update fails.

ex:$todaydt = date('m-d-y H:i:s');
list ($month, $day, $year) = split ('[/.-]', $todaydt);
$todaydt= date ("d M Y H:i:s", mktime (9,10,11,$month,$day, $year));

$sql = "Insert into xx_tbl(ref_id, entrydt)
values ('$ref_num','$todaydt');

The error message I get is,
OCIStmtExecute: ORA-01722: invalid number in ....line#....

For date only update in the same field I used
$todaydt= date ("d M Y", mktime (9,10,11,$month,$day, $year));
It inserted fine.
But not when I add time format. I used different ways,
$todaydt= date ("Y m d H:i:s", mktime (9,10,11,$month,$day, $year));

Could someone direct me where I am doing wrong?

Thanks
-Satya

    Oracle has a to_date function that allows you to convert from a specified format into the Oracle understandable date format. The function takes two parameters: the data you are trying to convert, and the format that data is in. You'd use it like:

    to_date('02-27-01 08:15:00','mm-dd-yy hh:mi:ss')
    OR
    $todaydt= date ("Y m d H:i:s", mktime (9,10,11,$month,$day, $year));
    to_date ('$todaydt','yyyy mm dd hh:mi:ss')

    You should be able to use it whenever you need to insert into or update your table.

    Insert into OracleTable (OracleDateColumn) Values (to_date('$datestring','$format'))

      Thank you

      It is working only the hour is less than 13.

      When I use small 'h' to get lessthan 13 format it is inserting but problem is, for 2:30:00PM it is inserting 2:30:00 only ,not 14:30:00.

      I tried using AM format in date function but while insering, to_date() is not recognizing it.

      Do you have any ideas? Do I need to change any format in to_date()?

      Thanks

        You can use a 24 hour variation like so:

        to_date('02-27-01 14:30:00','mm-dd-yy hh24:mi:ss')

          Or, like this:

          to_date('02-27-01 14:30:00 A.M.', 'mm-dd-yy hh:mi:ss A.M.')

            10 days later

            Umm..

            If you're trying to <i>timestamp</i> the database entries with current date and time, why not just use <b>SYSDATE</b> on the insert and/or update? Wouldn't that be easier than getting the current time from the OS and then converting it into Oracle date format?

            <PRE>
            INSERT INTO TEST_TABLE (COLUMN_A,COLUMN_B,DATETIME)
            VALUES ('blah blah','blah blah 2',SYSDATE);
            </PRE>

            or

            <PRE>
            UPDATE TEST_TABLE SET DATETIME = SYSDATE
            WHERE COLUMN_A = 'blah blah';
            </PRE>

            • Brian
              Write a Reply...