question

FaqeerDanish-8987 avatar image
0 Votes"
FaqeerDanish-8987 asked EchoLiu-msft edited

How do I manually cast value to datatype of column?

I'm working with a table of user input that I'm trying to compare to the current values in the table within SQL.

I'm trying to get the rows where the items are different than what's in the table, but the issue I'm running into is an input value may be 2 when the value in the table is casted as a DECIMAL so the actual value in the table is 2.00. I want to consider those to be the same.

My thought process is that I want to somehow cast my input into the datatype of the column, then compare.

My Table


ColumnName | Value


<user-id> | 1
<user-date> | 2021-06-30T00:00:00
Incoming Values


ColumnName | Value


<user-id> | 1
<user-date> | 2021-06-30


sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @FaqeerDanish-8987,

Welcome to the microsoft TSQL Q&A forum!

Your question is related to tsql, so my colleague helped you add the tsql tag.

Regarding the conversion syntax, cooldadtx has provided you with a detailed answer.

 SELECT DISTINCT SQL_VARIANT_PROPERTY(yourcolumn, 'BaseType')
 FROM yourtable

The above code can return the data type of a certain field for you, and may also be useful to you.


If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cooldadtx avatar image
0 Votes"
cooldadtx answered JeffreyWilliams-3310 commented

To do a straight cast in SQL (meaning the value better be correct) then use the CAST function. It treats the given value as the given type for purposes of comparison and whatnot.

CAST(@inputValue AS DECIMAL(10, 5))


But please note that SQL should do the right thing for most types except strings. So if you have a numeric 2 and you compare that to a decimal 2 they should be equal. No need for a cast. Refer to the earlier link for the conversion rules that SQL follows.

DECLARE @decimalValue AS DECIMAL = 2
DECLARE @inputValue AS INT = 2

IF @decimalValue = @inputValue
   PRINT 'Are Equal'
ELSE
   PRINT 'Are Not Equal'


For cases where you need to convert a value (say a string to an integral) then use CONVERT instead. Again refer to the docs linked earlier.

DECLARE @inputValue AS VARCHAR(20) = '2'
SELECT CONVERT(DECIMAL(10, 5), @inputValue)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Data type precedence comes into play here - and this example would still evaluate to true in SQL Server because the string '2' would be implicitly converted to the decimal data type. Where this would fail is if the value cannot be implicitly converted to the associated data type. If that is possible, then use either TRY_CAST or TRY_CONVERT for the comparison, which will then return a NULL value if the data cannot be converted to that data type.

  Select iif(2.00 = '2', 'True', 'False')
       , iif(2.00 = try_cast('2d' As decimal(3,2)), 'True', 'False')
0 Votes 0 ·