How to insert data in a 'DATE' type sql

Question

I have created tables like this,

create table customer
(
   customer_id number(3),
   customer_name varchar(20),
   phone varchar(15)
);

create table manager
(
   customer_id number(3),
   ordered_products number(4),
   transaction_on DATE
);

Now how will i insert data in manager table? How to insert into the DATE type?

`INSERT INTO manager (transaction_on) VALUES ('2000-02-28');` — ta.speot.is, Mar 07 '18 at 05:50
You should tell us which database (e.g. MySQL, SQL Server, Oracle, etc.) you are using, but yes the above comment should work for you. — Tim Biegeleisen, Mar 07 '18 at 05:52

Shubham Pandey · Accepted Answer · 2018-03-07T06:56:36.353

The answer to this really depends on the type of sql you are using.

In general if the date time is present in the ISO format (yyyymmdd), any sql engine should not face any issues in parsing it. A custom date format would be tricky and specific to individual SQL clients, but here are the broad types to help you get started:

Oracle/PostgreSQL

INSERT INTO manager
(
    transaction_on
)
SELECT TO_DATE('23-01-2018', 'dd-mm-yyyy')
FROM DUAL;

This will allow you to parse any date format, as long as you can specify the date format in the following string. The values I have provided are only examples, you can play around with them as per your specific use case. Oracle Reference, Postgres Reference

SQL Server

INSERT INTO manager
(
    transaction_on
)
SELECT CONVERT(DATE, '02-25-2018', 101);

Here 101 is style indicator from a pre-specified list, which supports a wide range of date formats. You can select the style indicator based on your specific input string format

MySQL

INSERT INTO manager
(
    transaction_on
)
SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y');

This is pretty similar to usage in Postgres and Oracle, except that the date format is represented slightly differently. My SQL Reference

score 0 · Answer 2 · answered Mar 07 '18 at 05:54

0

You can insert via SQL Statement:

INSERT INTO manager (customer_id, ordered_products, transaction_on)
VALUES (123, 2, '2018-03-07 05:52:30');

answered Mar 07 '18 at 05:54

Habeeb

7,601
1
30
33

4

This is a dangerous solution because it depends on the NLS settings of the client. Using `to_date()` an ANSI SQL date literal is much preferred over the evil implicit type casting – Mar 07 '18 at 06:54

score 0 · Answer 3 · answered Mar 07 '18 at 05:54

We can provide more accurate answer if we know the database type. (etc. MySQL, MSSQL, PosgreSQL, ...)

As a generic answer: This would probably work:

 INSERT INTO manager (
    customer_id,
    ordered_products,
    transaction_on) 
 VALUES (
    001,
    5,
    '2000-02-28'
 );

score 0 · Answer 4 · answered Mar 07 '18 at 06:07

In MySQL, you can do it in two ways if you want to insert the current date

INSERT INTO manager (customer_id, ordered_products, transaction_on) VALUES (NULL, '110', NOW());

or for any date you can use

INSERT INTO manager (customer_id, ordered_products, transaction_on) VALUES (NULL,'10', '2018-03-07');

score -1 · Answer 5 · answered Mar 07 '18 at 06:01

In SQL Server if you want the date to be inserted automatically when the transaction occurs.

ALTER TABLE manager ADD CONSTRAINT DF_DefaultTranDate DEFAULT GETDATE() FOR  transaction_on

Whenever a row is inserted in the manager table the transaction_on column will have the server's date and time.

How to insert data in a 'DATE' type sql

5 Answers5