CODEWITHSUNDEEP
phpsqloracle-databasedatepickerstrtotime
Ronald
Ronald

Reputation: 557

Oracle SQL and strtotime - updating record

When I update a record, I am occasionally getting the year of 1969 being inserted into my field. The year updated from 2016 to 1969 ?? IS there a STRTOTIME issue with the date? I am not sure where the issue lies ...

  1. I am selecting date from the table

     SELECT to_char(a.MOV_DATE, 'DD-Mon-YYYY') as MOV_DATE FROM SCHOOL.MOV a

  2. I am inserting the date into a JQuery Datepicker input...

     <input type="text" name="movdat" id="datepicker" class="boxit" required readonly="readonly" style="cursor:pointer; background-color: #FFFFFF; width:140px;" value='<?php echo $objResult["MOV_DATE"]; ?>'>

datepicker:

     <script>
       $(function() {
         $("#datepicker").datepicker({ 
           dateFormat:'dd-M-yy',
           maxDate: 0
         }).val();
        });
      </script>

So the format from Oracle is DD-Mon-YYYY and from the datepicker is dd-M-yy

dd ( 2 digit day )

M ( month - short )

yy ( 4 digit year )

so they look like they match.

  1. PHP - store date from datepicker and extract year

    $f4 = $_POST['movdat']; // new date

$f5 = date('Y', strtotime($f4)); // year

  2. Update Record

      $sql = oci_parse($conn,"UPDATE SCHOOL.MOV SET MOV_DATE=:mdat,
                                                YEAR=:ye
                                            WHERE id='265'");


  oci_bind_by_name($sql, ':mdat', $f4);
  oci_bind_by_name($sql, ':ye', $f5);

  $objExec = oci_execute($sql, OCI_DEFAULT);

  if ($objExec) {  
     oci_commit($conn);
  }

Upvotes: 2

Views: 563

Answers (1)

timclutton
timclutton

Reputation: 13014

Try using the to_date function to explicitly tell Oracle in which format to expect the input string and convert it to a date object for internal processing:

UPDATE SCHOOL.MOV SET
  MOV_DATE = to_date(:mdat, 'DD-Mon-YYYY'),
  YEAR = :ye
WHERE
  id = '265'

Upvotes: 1

Related Questions