I am creating a table variable in T-SQL and encountering the "Conversion failed when converting date and/or time from character string", despite the fact that I'm not converting anything and the column in question contains no date data.

Code below:

Declare @table2 (variable1 bigint, variable2 varchar(255), variablen basetypen)

insert into @table2
Select Variable1, variable2 

Everything is working, except inserting variable2. Selecting works fine, and I've checked every single value (there only are 10) and none of them contains a date or time value, or anything which looks like one. (Can't post text because it would be identifying/proprietary). In fact, every one has exactly the same value.

What's going on here? I feel as though I might be doing something silly...


You didn't post your actual code, but SQL Server isn't throwing that message if there isn't an actual conversion error.

You say you aren't converting anything, but if you insert data of one data type into a column of another data type SQL Server will try to convert it implicitly.

Look at Data Type Conversion for more information:

Implicit conversions are not visible to the user. SQL Server automatically converts the data from one data type to another. For example, when a smallint is compared to an int, the smallint is implicitly converted to int before the comparison proceeds.

That would happen not only with comparison but also with insert statements

  • 1
    Hi Tom - yes, correct. That's what I thought, but couldn't identify the cause of the conversion. Following prompting from you to look for implicit conversion, identified this as being due to the alignment of the columns. As I said, I thought I must be missing something obvious! Regarding not pasting the code - unfortunately that's difficult to do as it contains terms and phrases which would make my company identifiable to anyone who works in our industry. I assume this is a common issue on this site, although I do understand why it's preferable to post it. Oct 7 '16 at 16:40

Not the answer you're looking for? Browse other questions tagged or ask your own question.