[sql] How to insert date values into table

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.

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 

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'));

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');    

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.

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.

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')