how to insert current date into a DATE field in dd

Question

I have a field in my table with datatype as DATE in Oracle. I want to insert the current date into that field, in format DD/MM/YYYY format.

I tried the below query:

select to_date(to_char(sysdate,'dd/mm/yyyy'),'dd/mm/yyyy') from dual

But it gives

1/8/2011 12:00:00 AM.

I want it to insert and show as

08/01/2011 12:00:00 AM.

Can anyone help me in this please ?

Answer 1

When the column is of type DATE, you can use something like:

Insert into your_table(your_date_column) Select TRUNC(SYSDATE) from DUAL;

This removes the time part from SYSDATE.

Answer 2

Alternatively, if you want to retrieve the date part of the DATE field, you may use truncate, i.e.

select to_char(trunc(sysdate),'dd/mm/yyyy') from dual;

Answer 3

Maybe this can help

insert into pasok values ('&kode_pasok','&kode_barang','&kode_suplier',
to_date('&tanggal_pasok','dd-mm-yyyy'),&jumlah_pasok);

note: '&' help we to insert data again, insert / end than enter to insert again example: Enter value for kode_pembelian: BEL-E005 Enter value for kode_barang: ELK-02 Enter value for kode_customer: B-0001 old 2: '&kode_pembelian','&kode_barang','&kode_customer', new 2: 'BEL-E005','ELK-02','B-0001', Enter value for tanggal_pembelian: 24-06-2002 Enter value for jumlah_pembelian: 2 old 3: to_date('&tanggal_pembelian','dd-mm-yyyy'),&jumlah_pembelian) new 3: to_date('24-06-2002','dd-mm-yyyy'),2)

1 row created.

SQL> / (enter)

Answer 4

DATE is a built-in type in Oracle, which is represented in a fixed way and you have no control over it.

So:

I want it to insert [...] as 08/01/2011 12:00:00 AM

The above is nonsensical. You don't insert a string, you insert a date.

Format is useful only when you want:

• to convert a string to an internal representation of date with TO_DATE (format mask: how to parse the string);
• to convert an internal representation of date to a string with TO_CHAR (format mask: how to render the date).

So basically, in your example you take a DATE, you convert it to a STRING with some format, and convert it back to DATE with the same format. This is a no-op.

Now, what your client displays: this is because your Oracle Client won't display DATE fields directly and the NLS layer will convert any DATE field that is selected. So it depends on your locale by default.

What you want is SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY') FROM dual; which will explicitly perform the conversion and return a string.

And when you want to insert a date in a database, you can use TO_DATE or date literals.