Insert system date in oracle

Question

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

When using the following query, it inserts the system date as 03/04/0013. I need 04/04/2013. Can you help me 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 with the direct entry of '02 / 04/2012 'it takes the same value as '02 / 04/2012'.

Answer 1

You must not activate TO_DATE on a date

sysdate already the 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 , not YYYY .

The date in oracle is a number representing the date and time, it does not have a “format” if you want to insert sysdate without the time value that 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 2

If the last field in your insert is a date type field, you don't need to convert SYSDATE, so the following should be fine:

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

But if this is a varchar field, then 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 3

I think the display format is related to the environment setting. TO_CHAR (SYSDATE, "DD / MM / YYYY") CAN YOU Insert a system date in DD / MM / YYYY format, but this is no longer a date type.