CODEWITHSUNDEEP
oracle10g
Gaurav
Gaurav

Reputation: 103

how to insert the current system date and time in oracle10g database

I have created a table with a column date_time type (varchar2 (40) ) but when i try to insert the current system date and time the doesnt work it gives error (too many values). please tell me what's wrong with the insert statement. 

create table HR (type varchar2 (20), raised_by number (6), complaint varchar2 (500), date_time varchar2(40))


insert into HR values ('request',6785,'good morning',sysdate,'YYYY/MM/DD:HH:MI:SSAM')

Upvotes: 0

Views: 12007

Answers (3)

Alex Poole
Alex Poole

Reputation: 191380

The immediate cause of the error is that you have too many values, as the message says; that is, more elements in your values clause than there are columns. It is better to explicitly list the column names to avoid future problems and confusion, so you're really doing this:

insert into HR (type, raised_by, complaint, date_time)
values ('request',6785,'good morning',sysdate,'YYYY/MM/DD:HH:MI:SSAM')

... sp you have four columns, but five values. You're trying to insert the current date/time as a string so you would need to use the to_char() function:

insert into HR (type, raised_by, complaint, date_time)
values ('request',6785,'good morning',
  to_char(sysdate,'YYYY/MM/DD:HH:MI:SSAM'))

But it is bad practice to store a date (or any other structured data, such as a number) as a string. As the documentation notes:

Each value manipulated by Oracle Database has a data type. The data type of a value associates a fixed set of properties with the value. These properties cause Oracle to treat values of one data type differently from values of another. For example, you can add values of NUMBER data type, but not values of RAW data type.

If you use a string then you can put invalid values in. If you use a proper DATE data type then you cannot accidentally put an invalid or confusing value in. Oracle will also be able to optimise the use of the column, and will be able to compare values safely and efficiently. Although the format you're using is better than some, using string comparison you still can't easily compare two values to see which is earlier, so you can't properly order by the date_time column for example.

Say you inserted two rows with values 2013/11/15:09:00:00AM and 2013/11/15:08:00:00PM - which is earlier? You need to look at the AM/PM marker to realise the first one is earlier; with a string comparison you'd get it wrong because 8 would be sorted before 9. Using HH24 instead of HH and AM avoids that, but would still be less efficient than a true date.

If you need to store a date with a time component you can use the DATE data type, which has precision down to the second; or if you need fractional seconds too then you can use TIMESTAMP. Then your table and insert would be:

create table HR (type varchar2 (20), raised_by number (6),
  complaint varchar2 (500), date_time date);

insert into HR (type, raised_by, complaint, date_time)
values ('request',6785,'good morning',sysdate);

You can still get the value in the format you wanted for display purposes as part of a query:

select type, raised_by, complaint,
  to_char(date_time, 'YYYY/MM/DD:HH:MI:SSAM') as date_time
from HR
order by date_time;

TYPE                  RAISED_BY COMPLAINT            DATE_TIME           
-------------------- ---------- -------------------- ---------------------
request                    6785 good morning         2013/11/15:08:44:35AM

Only treat a date as a string for display.

Upvotes: 3

jai
jai

Reputation: 21897

Use TIMESTAMP datatype for date_time. And while inserting use the current timestamp.

create table HR (type varchar2(20), raised_by number(6), complaint varchar2(500), date_time timestamp);


insert into HR values ('request',6785,'good morning', systimestamp);

For other options: http://psoug.org/reference/timestamp.html

Upvotes: -1

Tech Mahesh
Tech Mahesh

Reputation: 392

You can use TO_DATE() or TO_TIMESTAMP or To_char() function,

insert into HR values ('request',6785,'good morning',TO_DATE(sysdate, 'yyyy/mm/dd hh24:mi:ss'))

insert into HR values ('request',6785,'good morning',TO_TIMESTAMP(systimestamp, 'yyyy/mm/dd hh24:mi:ss'))

sysdate - It will give date with time.

systimestamp - It will give datetime with milliseconds.

To_date() - Used to convert string to date. To_char() - Used to convert date to string.

Probably here you have to use To_char() because your table definition have varchar type for date_time column.

Upvotes: 0

Related Questions