Converting Datatypes using UPDATE

Other SQL Server Topics
1

Hello,

I am new to SQL and am trying to get the data I am working with cleaned up, so I hope this is a simple question.

I am using an Update statement to convert an invalid date to say "Not Finalized". Instead of
2015-12-02 00:00:00.000 2015-06-04 00:00:00.000 NULL -30214 -30089

Instead I keep getting this error

Msg 8114, Level 16, State 5, Line 3
Error converting data type varchar to float.

Here is the code I am using:

UPDATE dbo.DataList
SET DeliveryDaysPerContract = 'Not Delivered', DelDaysPerDelNumber = 'Not Finalized'
WHERE DeliveryDaysPerContract < -20000

2

Assuming [DeliveryDaysPerContract] and [DelDaysPerDelNumber] are defined as having datatype numeric (INTeger perhaps) you cannot store a text string in them. My advice would be NOT to try to store sometimes-number and sometimes-string in the same column. Much better to have a Numeric column only store numeric values, and then you can use it for arithmetic functions etc. If necessary have another column - which can store/display Status or Message or similar.

You could:

Store NULL in the column if the value is invalid. Note that if you replace

SET DeliveryDaysPerContract = NULL
WHERE DeliveryDaysPerContract < -20000

you will lose the original value, so if you ever wanted to check what it was, or if something went wrong in the program, you wouldn't be able to ...

Add a "Status" column - e.g. where 1=OK, 2="Not delivered" etc.

Add a Computed Column which provides the description (based on whether [DeliveryDaysPerContract] < -20000 or not)

Create a VIEW which returns all the appropriate columns from the table and ALSO additional column(s) for the DeliveryDaysPerContract_DESCRIPTION and DelDaysPerDelNumber_DESCRIPTION

Add a TRIGGER which does a ROLLBACK (and displays an error message) if ever there is an attempt to store [DeliveryDaysPerContract] < -20000 (or any other goofy data). You could put a CONSTRAINT on the table to do the same thing - it depends on how you wan to trap, display and control the error state. That would prevent any goofy data getting into your database (but the current requirement may not fall in the "goofy data" category?!!)

1 Like