SQLTeam.com | Weblogs | Forums

Insert field varchar in field datetime


#1

I need to insert table1 data to table2

a table 1 field is varchar I want to insert into table2 datetime

example

Table 1 Col1 = 2016-06-20 00: 00: 00.000

when inserting the system shows me the following message:

Msg 242, Level 16, State 3, Procedure Bdi_Insert_BDI_Absence1, Line 9
The conversion of a nvarchar data in datetime data type created a value out of range.
The statement has been terminated.


#2

you need to convert the varchar value to datetime using the convert function and then insert the converted value.
How are you inserting the data?


#3

i insert with convert(datetime, col1)
i insert the field without convert

it does not work


#4

You have strings that are not valid dates. You need to either correct them, or skip them and put NULL in the column if the date isn't valid:

INSERT INTO table2 ( col2, ... )
SELECT CASE WHEN ISDATE(col1) THEN col1 ELSE NULL END AS col2, ...
FROM table1