How to change default date,timestamp dataype for columns in oracle

Question

I have created a table in Oracle in which I have KPI_START_DATE column which is a Date datatype, and KPI_START_TIME which is a TIMESTAMP datatype. Now I want to modify this date dataype for

KPI_START_DATE to dd/mm/yyyy

and

KPI_START_TIME to HH:MI:SS.

So that user should always enter the date and time in this column in this proper format.

I tried below query but its was giving error:

Alter table KPI_DEFINITION MODIFY(to_char(KPI_START_DATE,'dd/mm/yyyy') )

Answer 1

DISPLAYING and STORING are NOT the same when it comes to DATE.

When people say Oracle isn’t storing the date in the format they wanted, what is really happening is Oracle is not presenting the date in the character string format they expected or wanted.

When a data element of type DATE is selected, it must be converted from its internal, binary format, to a string of characters for human consumption. The conversion of data from one type to another is known as known a “conversion”, “type casting” or “coercion”. In Oracle the conversion between dates and character strings is controlled by the NLS_DATE_FORMAT model. The NLS_DATE_FORMAT can be set in any of several different locations, each with its own scope of influence.

I could go on with my leacture over DATE data type, but I am glad that someone has already got a good writeup over this. Please read this https://edstevensdba.wordpress.com/2011/04/07/nls_date_format/

Answer 2

DATE and TIMESTAMP columns do not have any inherent readable format. The values are stored in Oracle's own internal representation, which has no resemblance to a human-readable date or time. At the point to retrieve or display a value you can convert it to whatever format you want, with to_char().

Both DATE and TIMESTAMP have date and time components (to second precision with DATE, and with fractional seconds with TIMESTAMP; plus time zone information with the extended data types), and you should not try to store them separately as two columns. Have a single column and extract the information you need at any time; to get the information out of a single column but split into two fields you could do:

select to_char(KPI_START, 'dd/mm/yyyy') as KPI_START_DATE,
  to_char(KPI_START, 'hh24:mi:ss') as KPI_START_TIME

but you'd generally want both together anyway:

select to_char(KPI_START, 'dd/mm/yyyy hh24:mi:ss')

Also notice the 'hh24' format model to get the 24-hour clock time; otherwise you wouldn't see any difference between 3 a.m. and 3 p.m.

You can store a value in either type of column with the time set to midnight, but it does still have a time component - it is just midnight. You can't store a value in either type of column with just a time component - it has to have a date too. You could make that a nominal date and just ignore it, but I've never seen a valid reason to do that - you're wasting storage in two columns, and making searching for and comparing values much harder. Oracle even provides a default date if you don't specify one (first day of current month). But the value always has both a date and a time part:

create table KPI_DEFINITION (KPI_START date);
insert into KPI_DEFINITION (KPI_START)
  values (to_date('27/01/2015', 'DD/MM/YYYY'));
insert into KPI_DEFINITION (KPI_START)
  values (to_date('12:41:57', 'HH24:MI:SS'));
select to_char(KPI_START, 'YYYY-MM-DD HH24:MI:SS') from KPI_DEFINITION;

TO_CHAR(KPI_START,'YYYY-MM-DDHH24:MI:SS')
-----------------------------------------
2015-01-27 00:00:00                       
2015-01-01 12:41:57                       

Your users should be inserting a single value with both date and time as one:

insert into KPI_DEFINITION (KPI_START)
  values (to_date('27/01/2015 12:41:57', 'DD/MM/YYYY HH24:MI:SS'));
select to_char(KPI_START, 'YYYY-MM-DD HH24:MI:SS') from KPI_DEFINITION;

TO_CHAR(KPI_START,'YYYY-MM-DDHH24:MI:SS')
-----------------------------------------
2015-01-27 12:41:57  

You can also use date or timestamp literals, and if using to_date() you should always specify the full format - don't rely on NLS settings as they may be different for other users.

Answer 3

You should understand difference between datatype and format. DATE is a datatype. TIMESTAMP is a datatype. None of them have formats, they're just numbers.

When converting character datatype to or from date datatype, format should be applied. It's an attribute of an actual conversion, nothing else.

Look at this:

SQL> create table tmp$date(d date);

Table created

SQL> insert into tmp$date values (DATE '2010-11-01');

1 row inserted

SQL> insert into tmp$date values (DATE '2014-12-28');

1 row inserted

SQL> select d, dump(d) from tmp$date;

D           DUMP(D)
----------- ---------------------------------
01.11.2010  Typ=12 Len=7: 120,110,11,1,1,1,1
28.12.2014  Typ=12 Len=7: 120,114,12,28,1,1,1

There is no any 'format' here.