CODEWITHSUNDEEP
sqlsql-server
DeveloperLV
DeveloperLV

Reputation: 1781

How to insert custom date into table in SQL Server?

Here is some values from a specific column in SQL Server: 

1899-12-30 00:00:00.0000000
1899-12-30 00:00:00.0000000
2022-03-01 00:00:00.0000000
2022-03-01 00:00:00.0000000

Column type is datetime2(7)

What is the correct way of creating an insert statement to insert a specific date?

Is this method okay?:

insert into table1 (date) values ('2020/01/31')

Is this acceptable? Or should it be:

insert into table1 (date) values ('2020/01/31 00:00:00.0000000')?

Using SQL Server

Upvotes: 1

Views: 2466

Answers (4)

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

For all versions of SQL Server (up until this point in time, SQL Server 2019) the following string format is the "standard" way of inserting string dates into date columns in SQL Server.

INSERT INTO TABLE (DateColumn) VALUES ('20200131');

Where the 20200131 value corresponds to 2020 01 31 (year, month, date without spaces).

Upvotes: 1

Fedia Boughattas
Fedia Boughattas

Reputation: 21

we can use BOTH

INSERT INTO MyTable
           (Date)
       VALUES
           ('2022-03-01')
 GO

Upvotes: 0

PaulS
PaulS

Reputation: 940

Both are acceptable, although the syntax for datetime2 is YYYY-MM-DDThh:mm:ss[.nnnnnnn].

DATE only   The TIME part defaults to 00:00:00.
TIME only   The DATE part defaults to 1900-1-1.
DATE + TIME Trivial
DATE + TIMEZONE Not allowed.
DATE + TIME + TIMEZONE  The local DATETIME will be used.

Complete list of allowed formats

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use standard date format YYYY-MM-DD :

insert into table1 (date) 
    values ('2020-01-31')

Upvotes: 1

Related Questions