Getting Conversion failed when converting the varchar value ' --> ' to data type int. How do I solve this?

Question

1.00/5 (1 vote)

See more:

Hey guys, I made a trigger for when a table is updated. This is a bit of its code:

SQL

INSERT INTO [dbo].Logs([date],[name],[changes],[eventId])
             SELECT getdate(), 'john',
             CAST(D.eventStart AS nvarchar(30)) + ' --> ' + CAST(I.eventStart AS nvarchar(30)),
             I.id
             FROM Inserted I
                  INNER JOIN Deleted D ON I.id = D.id

I'm using the CAST because eventStart is of the type dateTime2

The problem is when I run the application I get:

"Conversion failed when converting the varchar value ' --> ' to data type int."

Any idea why and how to fix this?

Posted 10-Mar-15 1:18am

varmartins

Add a Solution

Comments

Herman<T>.Instance 10-Mar-15 7:22am

Have you ever seen a valid date accepting '-->' in it's contents?

ZurdoDev 10-Mar-15 7:31am

What data type is [changes]?

varmartins 10-Mar-15 7:39am

changes is a String (nvarchar(max)) . What it's doing is not putting ' --> ' into a date but 2 dates and ' --> ' into a String supposedly

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2015-03-10T01:44:00

Solution 1

Check your field types: the most likely reason is that changes is not an NVARCHAR column, so your two dates bolted together are being converted to whatever datatype that is.

A quick check locally:

SQL

SELECT CAST(CAST(GETDATE() AS DATETIME2) AS NVARCHAR(30)) + '-->' + CAST(CAST(GETDATE() AS DATETIME2) AS NVARCHAR(30))

Shows a perfectly valid string:

2015-03-10 11:42:52.2230000-->2015-03-10 11:42:52.2230000

So it has to be part of the INSERT operation.

Posted 10-Mar-15 1:44am

OriginalGriff

Comments

varmartins 10-Mar-15 7:53am

changes is a nvarchar(MAX) that's certain. What else could it be?

OriginalGriff 10-Mar-15 8:06am

Try running your query (as a SELECT rather than INSERT for safety) in SSMS directly, and see if you get the same problem - if you do, cut it down until the problem goes away and then look at the last bit that failed.

SQL Server error reporting is primitive at best! :laugh:

Just a thought - are either of your eventstart columns null allowed?

varmartins 10-Mar-15 8:07am

No, they don't allow null. Ok I'll try debugging this.

OriginalGriff 10-Mar-15 8:24am

Didn't think they would - but null values can throw some very odd exceptions with SQL so it was worth a look! :laugh: