2

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?

Saswat
  • 12,320
  • 16
  • 77
  • 156
  • $time contains the string '2017-02-17 23:10:00', but it gets saved in Oracle as 12-Feb-17? Is this a typo or is this really another day? – Thorsten Kettner Feb 12 '17 at 17:56
  • @ThorstenKettner . . . I think 12-Feb-17 is just the default way that Oracle shows a date. – Gordon Linoff Feb 12 '17 at 18:00
  • 1
    @Gordon .. No. I think it's based on session's NLS settings. – Gurwinder Singh Feb 12 '17 at 18:01
  • @Gordon Linoff: Anyway, when trying to save February 17, this shouldn't store as February 12. – Thorsten Kettner Feb 12 '17 at 18:02
  • I strongly suspect that the value of `$time` is not what you think it is. Let's have an understanding - when you think you've found a bug in the basic functionality of a widely used piece of software you are probably correct - but the bug is very likely in your understanding of what's going on, not in the software. Given that today is 12-Feb-2017 I think you have to allow for the possibility that `$time` just *might* have a value of `12-Feb-2017`. – Bob Jarvis - Слава Україні Feb 12 '17 at 19:27

3 Answers3

2

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;
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
  • It seems the OP wanted `'yyyy-mm-dd'` (rather than `Mon`, in particular, but also the order); otherwise this is the right answer. –  Feb 13 '17 at 00:12
1

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • What does that mean? *When you just select the date, Oracle doesn't show the time component*? That depends on the session's `NLS_DATE_FORMAT`, no? –  Feb 13 '17 at 00:10
0

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;
MT0
  • 143,790
  • 11
  • 59
  • 117
  • Interesting. Is that correct about Java/Python? I always assumed (I don't know either of those languages) that they have some sort of `date` data type, and Oracle will NOT pass strings to them unless requested in the string data type. This is different from SQL\*Plus and Toad, which only understand strings. Just wondering (my onw understanding of this may be 100% wrong.) –  Feb 13 '17 at 16:11
  • In Java, the Oracle JDBC driver will store an Oracle date in an [`oracle.sql.DATE`](https://docs.oracle.com/database/121/JAJDB/oracle/sql/DATE.html) class - this is just a Class representing the binary format Oracle uses internal to the database. If you want to display that to the user then it does not display the bytes, it will use the `toString`method (or some other similar method invoked by the user) to format the date in a way the user would understand. SQL*Plus and Toad are the same - Oracle just passes 7 bytes and leaves it to something else to format them so the user can understand. – MT0 Sep 14 '17 at 10:06