I was trying to insert a value into one of the columns with DATE datatype in Oracle DB through java.
Tried with the below
insertSurveyQuery.append("cast(to_date('12/31/8888', 'MM/dd/yyyy' )as date), ");
OP in Oracle DB:
DEC-31-88
but i want the date to be stored like 12/31/8888.
Any of your help is appreciated!
-Thanks!
This is how your SQL client display dates. You can alter this format by using:
alter session set NLS_DATE_FORMAT='MM/dd/yyyy';
Internally (in DB) DATE is stored numerically so it doesn't have any format.
If you have many users accessing your application, then you could do the following in Java code
Date newDate = new SimpleDateFormat("mm/dd/yyyy").parse(myDate);
and use newDate in insert or update statement.
By doing this you do not need to change client settings.
First you have to create table in the format of
create table t1
(
ename varchar(20),
address varchar(20),
hire_date date,
salary number(5),
dob (we enter as 'dd-mm-yyyy')
);
In which you will have to give the date column as dd-mm-yyyy type then you can set data as you want.
Oracle doesn't support the format of DD/MM/YYYY. If you want to store the date time or time stamp you have to use DD-MM-YYYY format.
In direct insert query:
SimpleDateFormat df = new SimpleDateFormat("dd-MM-yyyy' 'hh:mm:ss a");
String date = "01-01-1900 01:00:00 AM";
In Procedure :
create or replace
PROCEDURE insertDATE(
DATE_PARAM IN VARCHAR2,
)
IS
BEGIN
INSERT INTO CUSTOMER("DATE")
VALUES (TO_TIMESTAMP(DATE_PARAM,'DD-MM-YYYY HH:MI:SS AM'));
COMMIT;
END;
datecolumn does NOT have a "format". The SQL client (the application) formats the date value when displaying it. Just change the display format of dates in your SQL client.