CODEWITHSUNDEEP
sqloracle-databasedatetime
readJohn
readJohn

Reputation: 173

Inserting date&time, but only date is inserted

I'm trying to insert in a table the date and the time,but at the end only the date is insrted. here's the code:

create table myDate(date_value Date PRIMARY KEY );
INSERT INTO myDate(date_value ) VALUES (to_date('14/09/2010 18:00','dd/mm/yyyy hh24:mi'));

And I get only 14/09/2010 stocked in the table myDate.What's the problem(is there a way to do that without timestamp)?

Upvotes: 1

Views: 47

Answers (2)

EdStevens
EdStevens

Reputation: 3872

I think it worth noting that an alternatives to setting the session NLS parameter is the explicit use of the to_char function. I prefer the to_char because it leaves no doubt to anyone reading the code. Reliance on NLS can be iffy because it can be set at different levels. Besides, if you don't explicitly use to_char, oracle will still call it in the background, using the controlling setting of NLS_DATE_FORMAT.

SQL> -- create table
SQL> Create table mytest (dob date);

Table created.

Elapsed: 00:00:00.08
SQL> insert into mytest values (sysdate);

1 row created.

Elapsed: 00:00:00.03
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> select dob nls_default,
  2         to_char(dob,'dd-mm-yyyy') dob1,
  3         to_char(dob,'yyyy-mm-dd') dob2,
  4         to_char(dob,'dd-mm-yyyy hh24:mi:ss') dob3
  5  from mytest;

NLS_DEFAU DOB1       DOB2       DOB3
--------- ---------- ---------- -------------------
11-DEC-20 11-12-2020 2020-12-11 11-12-2020 11:45:31

1 row selected.

Elapsed: 00:00:00.03
SQL> drop table mytest purge;

Table dropped.

For more on the subject, see this article.

Upvotes: 2

Miguel
Miguel

Reputation: 2079

Depending on the tool you are using is most likely a display issue. you might want to try something like this

alter session set nls_date_format = 'DD-MON-YYYY HH12:MI:SS PM';

This will alter your sesssion to display the date with full timestamp. I use oracle sql developer https://www.oracle.com/database/technologies/appdev/sqldeveloper-landing.html If I don't set the date to my session while I'm writing a query I may see an unexpected date format. Let us know if that is the case for you as well.

Upvotes: 1

Related Questions