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

Question

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

Navigation

#1 by (2 votes)
#2 by (0 votes)

3

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

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

Orders 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 date-time result in an out-of-range value. The statement has been terminated.

sql sql-server sql-insert

asked by anonymous 20.11.2017 / 17:31

2 answers

Is it possible to omit the numbers of the formatting arguments of "String.Format" in C #? Create a Generic Adapter

score 2 · Answer 1

The correct date format is AAAA-MM-DD .

SqlFiddle

Fiddle with the convert function

score 0 · Answer 2

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

set dateformat dmy;

The complete code looks 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: