Storing only DATE I timestamp data type column [message #675550] |
Thu, 04 April 2019 16:08 |
azeem87
Messages: 116 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 #675553 is a reply to message #675550] |
Fri, 05 April 2019 00:03 |
|
Michel Cadot
Messages: 68659 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.
|
|
|