Inserting system date in oracle

Question

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'.

Answer 1

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'));

Answer 2

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))

Answer 3

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.