sql inserts the wrong datetime into the table

Question

i to using microsoft sql manager,and was trying to insert a date in my table,the problem is that it is entering the wrong date,i do the following command for the manager table

INSERT
INTO tblCliente /*minha tabela que eu criei*/
VALUES('Ze',1999-10-02,1) /*atributos da tabela:nome[nvarchar[50]],data[datatime],limiteCliente[decimal(18,2)]*/

but when clicking to run the table code,

someone with experience in sql could tell me how to solve this problem?

Answer 1

What happened is that the value 1999-10-02 was considered as a numerical expression:
1999 - 10 - 2 = 1987
And this numerical value was added to the lowest date value available for datetime, which is 1/1/1900. Something similar to:

-- código #3
SELECT dateadd(day, 1999-10-02, 0)

As the second column is declared as datetime, recommend to enter the value between apostrophes. For example:

-- código #1
INSERT into tblCliente
   VALUES('Ze', '19991002', 1);

That way, no hyphens.

You can also use the Convert function if you want to enter the date in another format. For example, to enter the date in the dd/mm/yyyy format:

-- código #2
INSERT into tblCliente
   VALUES('Ze', convert(datetime, '2/10/1999', 103), 1);

Answer 2

Try it this way

INSERT INTO tblClientes VALUES ('Zé', '1999-10-02', 1)

Date has to be entered as string.