Error with Insert in SQL Server: The Conversion of a varchar data type to a datetime data type resulted in an out-of-range value

Asked

Viewed 2,282 times

3

Next I have to do a survey and implement a query in the database using Merge and I have to do it using one of the scripts used in class.

The problem is that when I run the script the following error in insert and the table ends up being empty.

Order table

CREATE TABLE pedido (nr_pedido numeric(5) NOT NULL,
                                          dt_pedido date, nr_mesa numeric(4));

Insert

INSERT INTO pedido VALUES (1, GETDATE(), 527),
(2, '10/10/2009', 632),
(3, '21/05/2009', 606),
(4, '26/06/2009', 970),
(5, '05/05/2009', 606),
(6, '13/11/2009', 527),
(7, '01/03/2005', 181),
(8, '09/04/2010', 181);

After running the Insert the following error message appears:

Msg 242, Level 16, State 3, Line 67 The Conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.

sql database sql-server sql-insert
  • 4

    The correct date format is AAAA-MM-DD, that is to say, ano-mês-dia

  • I made a Fiddle! A little more in the documentation: Date and time data types

  • 1

    In addition to what was mentioned, you can change in your user who is logging into the database the default language. So it will accept the insertion of values in this format

2 answers

2

The correct date format is AAAA-MM-DD.

Sqlfiddle

Read more on: Date and time data types

If you want to select your date in the format dd/mm/aa, use the function CONVERT.

CONVERT(VARCHAR(10), seuCampo, 103).
  • 103 Indicates that the output format will be in the format: dd/mm/aaaa
  • 3 If you want the year with only 2 digits.

inserir a descrição da imagem aqui

Fiddle with Convert function.

  • opa vlw cara, I don’t understand a thing, in SQL Server that we use in college is in Portuguese and the scripts work normally including the date in DD/MM/YYYY format, but mine is in English will be that is what causes the conflict?

  • @Icaro takes a look at my edition, the language you are in your database has nothing to do with data type!

  • The advantage of using the CONVERT function is that the command is language independent of the SQL Server session. It’s spelled a little bit more but there’s a robust code.

0

In order for the execution of the code to be independent of the SQL Server installation language, and as in the INSERT statement the dates are in the dd/mm/yyyy format, one option is to add the following command before the INSERT:

set dateformat dmy;

The whole code goes like this:

-- código #1 v2
set dateformat dmy;
INSERT INTO pedido VALUES 
   (1, current_timestamp, 527), 
   (2, '10/10/2009', 632),
   (3, '21/05/2009', 606),
   (4, '26/06/2009', 970),
   (5, '05/05/2009', 606),
   (6, '13/11/2009', 527),
   (7, '01/03/2005', 181),
   (8, '09/04/2010', 181);

Another way is to use

set language brazilian;

before the command with the INSERT statement.

It is also possible to use the CONVERT function as mentioned by Marconi in another answer.

-- código #2
INSERT INTO pedido VALUES 
   (1, current_timestamp, 527), 
   (2, convert(date, '10/10/2009', 103), 632),
   (3, convert(date, '21/05/2009', 103), 606),
   (4, convert(date, '26/06/2009', 103), 970),
   (5, convert(date, '05/05/2009', 103), 606),
   (6, convert(date, '13/11/2009', 103), 527),
   (7, convert(date, '01/03/2005', 103), 181),
   (8, convert(date, '09/04/2010', 103), 181);

References:

Browser other questions tagged sql database sql-server sql-insert

You are not signed in. Login or sign up in order to post.