CODEWITHSUNDEEP
sqloracle-databasedatetimeoracle10gdate-formatting
AbIr Chanda
AbIr Chanda

Reputation: 739

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.

Upvotes: 63

Views: 1077249

Answers (7)

g78
g78

Reputation: 68

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.

Upvotes: -1

Ankush Chaudhary
Ankush Chaudhary

Reputation: 1

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

Upvotes: 0

Bhaskar Jat
Bhaskar Jat

Reputation: 5

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.

Upvotes: 0

lsandeep3
lsandeep3

Reputation: 29

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

Upvotes: 1

Zia Ullah
Zia Ullah

Reputation: 103

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

Upvotes: 4

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

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.

Upvotes: 66

Dinith
Dinith

Reputation: 1029

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

Upvotes: 15

Related Questions