CODEWITHSUNDEEP
oracle-database
user2238881
user2238881

Reputation: 101

Inserting system date in oracle

How to insert system date in dd/mm/yyyy to table using oracle 10g?

While using the following query, it inserts system date as 03/04/0013. I need 03/04/2013. Can you help me to solve this problem?

insert into GtTable 
values('111','Name',300,'Tour',to_date('02/04/2012','DD/MM/YYYY'),to_date(sysdate,'DD/MM/YYYY'));

But when inserting '02/04/2012' directly, it accepts as same as '02/04/2012'.

Upvotes: 10

Views: 74824

Answers (4)

Jorge Santos Neill
Jorge Santos Neill

Reputation: 1785

The solution that worked for me is the following

insert into method (id,name,created_by,updated_by, created_at, updated_at) values (1,'GET',0,0,TO_CHAR(SYSDATE,'DD/MM/YYYY hh:mm:ss'),null);

Upvotes: 0

Gentlezerg
Gentlezerg

Reputation: 286

I think the format of displaying is due to the environment parameter。 TO_CHAR(SYSDATE,'DD/MM/YYYY') CAN LET YOU inserts system date as DD/MM/YYYY ,but, it is not date type anymore.

Upvotes: 1

A.B.Cade
A.B.Cade

Reputation: 16915

You shouldn't activate TO_DATE on a date

sysdate is already a date, when you run TO_DATE with it as the first parameter, you make Oracle implicitly convert it to a string according to NLS_DATE_FORMAT which in your case probably contains YY and not YYYY.

A date in oracle is a number representing a date and time, it doesn't have a "format", if you want to insert sysdate without the time value you need to truncate it like this:

insert into GtTable 
values('111','Name',300,'Tour',to_date('02/04/2012','DD/MM/YYYY'),trunc(sysdate))

Upvotes: 16

warantesbr
warantesbr

Reputation: 660

If the last field on your insert is a date type field, you should not need any conversion on SYSDATE, so, the following should be OK:

insert into GtTable 
values('111', 'Name', 300, 'Tour', to_date('02/04/2012','DD/MM/YYYY'), sysdate);

But if it is a varchar field, the following should work:

insert into GtTable 
values('111', 'Name', 300, 'Tour', to_date('02/04/2012','DD/MM/YYYY'), to_char(sysdate, 'dd/mm/yyyy'));

Upvotes: 6

Related Questions