CODEWITHSUNDEEP
sqloracle-databasedatetime
Ronald
Ronald

Reputation: 557

Oracle Date Time Concatenation

I have 2 form fields - one captures the date in DD-MON-YY format and one form field that captures the time in HHMM (no colon) format - 24 hour.

I am trying to concatenate these and insert them into an Oracle Date/Time field

$sql = "INSERT INTO SCHEMA_A.MOVIES(MOVIEDATE) VALUES to_date(:dt||:tm, 'DD-MON-YY HH24MI');

$compiled = oci_parse($con, $sql);

oci_bind_by_name($compiled, ':dt', $f1); //date
oci_bind_by_name($compiled, ':tm', $f2); //time

oci_execute($compiled);

Am I doing this correctly?

and to retrieve the time... (and I will be doing some math with these time values as well)

 $sql = "SELECT to_char(MOVIEDATE, 'HH24MI') as MTIME FROM SCHEMA_A.TABLE_A...

and to retrieve just the date ...

$sql = "SELECT to_char(MOVIEDATE, 'DD-MON-YY') as MDATE FROM SCHEMA_A.TABLE_A...

just wondering if this is correct ??

Upvotes: 0

Views: 1900

Answers (1)

MT0
MT0

Reputation: 168361

$sql = "INSERT INTO SCHEMA_A.MOVIES(MOVIEDATE) VALUES to_date(:dt||:tm, 'DD-MON-YY HH24MI');

A couple of minor issues:

  • You are missing a closing double quotation mark.
  • You are inserting against the format mask DD-MON-YY HH24MI but the concatenated value does not have the separating space. You could just use the format mask DD-MON-YYHH24MI.
  • You are only inserting a date - is there not more associated data to be inserted along side the date (title of the movie, some sort of unique identifier, etc)?

Regarding, getting the date out of the database; why do you not just get the date value and then you can extract the date and time components within the scripting language you are using (rather than needing multiple trips to the database).

Upvotes: 1

Related Questions