Home » SQL & PL/SQL » SQL & PL/SQL » Storing only DATE I timestamp data type column (Oracle 12c)
Storing only DATE I timestamp data type column [message #675550] Thu, 04 April 2019 16:08 Go to next message
azeem87
Messages: 100
Registered: September 2005
Location: dallas
Senior Member
In an existing table with data type as timestamp, requirement is to store only date , and when I tried to insert ,by default it inserts timestamp., can we just store date only..
I understand while selecting we can apply format and display but while storing..
create table abc_test( X number,y timestamp);

Insert into abc_test  (x, y)
Values (1, TO_date('01/01/2019','mm/dd/yyyy'));

Insert into abc_test  (x, y)
Values (2,TO_TIMESTAMP('01/02/2019','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));

Insert into abc_test  (x, y)
Values (3, TO_date('01/03/2019','mm/dd/yyyy'));
commit;;

select * from abc_test;

         X Y                                                 
---------- --------------------------------------------------
         1 1/1/2019 12:00:00.000000 AM                       
         2 1/2/2019 12:00:00.000000 AM                       
         3 1/3/2019 12:00:00.000000 AM                       

3 rows selected.
Re: Storing only DATE I timestamp data type column [message #675551 is a reply to message #675550] Thu, 04 April 2019 16:16 Go to previous messageGo to next message
BlackSwan
Messages: 26649
Registered: January 2009
Location: SoCal
Senior Member
SQL> create table abc_test( X number,y timestamp);

Table created.

  1* insert into abc_test values(1,trunc(systimestamp))
SQL> /

1 row created.

  1* select x, to_char(y,'YYYY-MM-DD HH24:MI:SS') from abc_test
SQL> /

	 X TO_CHAR(Y,'YYYY-MM-
---------- -------------------
	 1 2019-04-04 00:00:00

SQL> 

Re: Storing only DATE I timestamp data type column [message #675553 is a reply to message #675550] Fri, 05 April 2019 00:03 Go to previous message
Michel Cadot
Messages: 66800
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table abc_test( X number,y timestamp);

Table created.

SQL> Insert into abc_test  (x, y)
  2  Values (1, TO_date('01/01/2019','mm/dd/yyyy'));

1 row created.

SQL> Insert into abc_test  (x, y)
  2  Values (2,TO_TIMESTAMP('01/02/2019','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));

1 row created.

SQL> Insert into abc_test  (x, y)
  2  Values (3, TO_date('01/03/2019','mm/dd/yyyy'));

1 row created.

SQL> commit;

Commit complete.

SQL> select * from abc_test;
         X Y
---------- ---------------------------------------------------------------------------
         1 01/01/2019 00:00:00.000
         2 02/01/2019 00:00:00.000
         3 03/01/2019 00:00:00.000

3 rows selected.
TIMESTAMP and DATE datatypes always have a time part which is set to 00:00 if you don't use it.
There is no pure date with no time datatype in Oracle.

Previous Topic: How To Retrieve string unto first pipe symbol in query
Next Topic: How to split one column with dates into two with periods (from-to)
Goto Forum:
  


Current Time: Sun Jan 26 13:13:50 CST 2020