CODEWITHSUNDEEP
c#sqlsql-server
z-combs
z-combs

Reputation: 13

varchar data type to a datetime data type resulted in an out-of-range value

So I'm writing a quick console program to collect rows of data from an old MySql database my company has and push the rows to a new Sql database. The first 300 or so rows have been fine but all of a sudden I am getting a

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

I've tried a number of ways to convert my datetimes now even though there seems to be nothing wrong with the conversion when I put it in a select.

IE:

DECLARE @INPUT varchar(30) = '09/25/2010 04:55:47'; 
SELECT CONVERT(datetime, @INPUT, 101);`

outputs 2010-09-25 04:55:47.000.

The conversion error is only showing up in the full Sql insert:

DECLARE @INPUT varchar(30) = '09/25/2010 04:55:47'; 
INSERT INTO TABLE(A_BUNCH_OF_OTHER_COLUMNS, DATETIME_COLUMN, MORE_COLUMNS) 
          VALUES (A_BUNCH_OF_OTHER_VALUES, CONVERT(datetime, @INPUT, 101), MORE_VALUES);`

I see no reason that this isn't working and after asking some of my coworkers, they see nothing wrong with the Sql either.

EDIT: Fixed the INSERT to reflect the actual statement.

Upvotes: 0

Views: 445

Answers (2)

z-combs
z-combs

Reputation: 13

If this is SQL Server please add the tag. What is @DT ? Are any A_BUNCH_OF_OTHER_COLUMNS | A_BUNCH_OF_OTHER_VALUES DateTimes? if so the conversion issue is likely there. – Alex K.

Thats where my problem was, there are 3 other datetime fields that are operating times that run over 24 hrs

Upvotes: 0

Crispy Holiday
Crispy Holiday

Reputation: 472

INSERT INTO TABLE(A_BUNCH_OF_OTHER_COLUMNS, CONVERT(datetime, @INPUT, 101), MORE_COLUMNS)

In this part you should specify the column of the table which you want to insert values to. You specified the CONVERT method which is not a column.

Upvotes: 1

Related Questions