How to insert date values into table
SqlOracleDatetimeOracle10gDate FormattingSql Problem Overview
How can I insert into table with different input using / ,with date datatype?
insert into run(id,name,dob)values(&id,'&name',[what should I write here?]);
I'm using oracle 10g.
Sql Solutions
Solution 1 - Sql
Since dob is DATE data type, you need to convert the literal to DATE using TO_DATE and the proper format model. The syntax is:
TO_DATE('<date_literal>', '<format_model>')
For example,
SQL> CREATE TABLE t(dob DATE);
Table created.
SQL> INSERT INTO t(dob) VALUES(TO_DATE('17/12/2015', 'DD/MM/YYYY'));
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM t;
DOB
----------
17/12/2015
A DATE data type contains both date and time elements. If you are not concerned about the time portion, then you could also use the ANSI Date literal which uses a fixed format 'YYYY-MM-DD' and is NLS independent.
For example,
SQL> INSERT INTO t(dob) VALUES(DATE '2015-12-17');
1 row created.
Solution 2 - Sql
date must be insert with two apostrophes' As example if the date is 2018/10/20. It can insert from these query
Query -
insert into run(id,name,dob)values(&id,'&name','2018-10-20')
Solution 3 - Sql
let suppose we create a table Transactions using SQl server management studio
txn_id int,
txn_type_id varchar(200),
Account_id int,
Amount int,
tDate date
);
with date datatype we can insert values in simple format: 'yyyy-mm-dd'
INSERT INTO transactions (txn_id,txn_type_id,Account_id,Amount,tDate)
VALUES (978, 'DBT', 103, 100, '2004-01-22');
Moreover we can have differet time formats like
DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MI:SS
SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS
Solution 4 - Sql
> insert into run(id,name,dob)values(&id,'&name',[what should I write > here?]);
insert into run(id,name,dob)values(&id,'&name',TO_DATE('&dob','YYYY-MM-DD'));
Solution 5 - Sql
You can also use the "timestamp" data type where it just needs "dd-mm-yyyy"
Like:
insert into emp values('12-12-2012');
considering there is just one column in the table... You can adjust the insertion values according to your table.
Solution 6 - Sql
I simply wrote an embedded SQL program to write a new record with date fields. It was by far best and shortest without any errors I was able to reach my requirement.
w_dob = %char(%date(*date));
exec sql insert into Tablename (ID_Number ,
AmendmentNo ,
OverrideDate ,
Operator ,
Text_ID ,
Policy_Company,
Policy_Number ,
Override ,
CREATE_USER )
values ( '801010',
1,
:w_dob,
'MYUSER',
' ',
'01',
'6535435023150',
'1',
'myuser');