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.
Best Answer
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/2015A 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.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')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-DDDATETIME - format: YYYY-MM-DD HH:MI:SSSMALLDATETIME - format: YYYY-MM-DD HH:MI:SS insert into run(id,name,dob)values(&id,'&name',[what should I writehere?]);
insert into run(id,name,dob)values(&id,'&name',TO_DATE('&dob','YYYY-MM-DD'));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.
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'); To insert the current date you can just use this GETDATE() function.
insert into run(id,name,dob) values(&id,'&name',GETDATE());
you can also use CURRENT_TIMESTAMP() function to insert current date and time.