In PHP, date('Y-m-d H:i:s') gives result like '2017-02-17 23:10:00' format.
Now, I want to save the result in ORACLE 10g. I am using Oracle 10g Xpress Edition.
Now, I am trying to save the Data like this.
$sql = "INSERT INTO tbl_language VALUES
(1, 'Hindi', TO_DATE('".$time."','yyyy-mm-dd hh24:mi:ss'))";
Where $time = date('Y-m-d H:i:s');
The date value is getting saved as 12-Feb-17. How can I get the date data saved in the desired form?
When you just select the date column, Oracle doesn't show the time component.
Try this:
select l.*,
to_char(datecol, 'YYYY-MM-DD HH24:MI:SS')
from tbl_languages l;
Date in Oracle is not saved as 2017-02-17 23:10:00 or 12-Feb-17 or whatever other format you think. It's stored in a totally different way. When you select the date from the database, the format in which the date is presented is based on your session's NLS_DATE_FORMAT settings.
If you want to get the date in a specific format, you can use TO_CHAR function with the required format:
select to_char(datecolumn, 'yyyy-mm-dd hh24:mi:ss') from your_table;
The DATE data type does not have a format; Oracle stores it as either 7- or 8-bytes and it is not until it is passed to a client program (i.e. SQL/Plus, SQL Developer, Toad, Java, Python, etc) and that client program formats it according to whatever rules it has that the date gets a format.
If you are using SQL/Plus or SQL Developer then it will use the NLS_DATE_FORMAT session parameter to format the date. You can change this using:
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
(Be aware that this will only change the format in the current session and will not change it for any other sessions/users.)
If you want to give the date a particular format then you will need to convert it to a string.
SELECT TO_CHAR( date_column, 'YYYY-MM-DD HH24:MI:SS' ) AS formatted_date
FROM tbl_language;