Implicit vs Explicit Data type conversion

What is a data type conversion? It is when changes from one data type to another. In SQL, we can simply use CAST and CONVERT functions to achieve this goal. This kind of data type conversion is called explicit data type conversion since we are intentionally and explicitly changing the data type.
However, in some scenarios, SQL Server automatically converts the data type to another, and this type of conversion is not visible to users. Hence, we call it implicit data type conversion. There are three situations in which an SQL Server will implicitly convert a data type to another.

1. Functions with Implicit Conversion

There are some functions whose main purpose/function is not converting data types, however while executing its function, it may trigger the implicit conversion
For example:


GETDATE() implicitly converts to date style 0 which is a datetime value with a date range from 1753-01-01 to 9999-12-31 and format of ‘YYYY-MM-DD hh:mm:ss.mmm’


SYSDATETIME() implicitly converts to date style 21 which is a datetime2(7) value with a date range from 0001-01-01 to 9999-12-31 and format of ‘YYYY-MM-DD hh:mm:ss.mmmmmmm’ which has a larger fractional precision than getdate().

REPLACE() implicitly converts to string.
This function replaces a substring or a string value with another string value. It only takes sting as input, so when the input is not string, it will automatically change the input to a string value first before it executes its main function.

2. Declaration Statement Overrides Assignment Statement


An implicit conversion will happen if you assign a value to a variable that does not align with its declared data type.
For example:

Although integer 1 is assigned to the variable, the data type of the variable is defined in the beginning, which is varchar. Hence, the integer 1 is converted implicitly to varchar 1 in the assignment statement.

3. Data Type with The Higher Precedence Rules

When an operator combines expressions of different data types, the SQL server will convert the data type with the lower precedence to the data type with the higher precedence regardless of the data type declaration.


Let’s take the previous example; however, this time, instead of adding varchar 1, we add integer 1.

This is because the integer is a data type with higher precedence; hence, SQL Server implicitly converts the varchar 1 to integer 1.


SQL Server uses the following precedence order for data types:

  1. user-defined data types (highest)
  2. sql_variant
  3. xml
  4. datetimeoffset
  5. datetime2
  6. datetime
  7. smalldatetime
  8. date
  9. time
  10. float
  11. real
  12. decimal
  13. money
  14. smallmoney
  15. bigint
  16. int
  17. smallint
  18. tinyint
  19. bit
  20. ntext
  21. text
  22. image
  23. timestamp
  24. uniqueidentifier
  25. nvarchar (including nvarchar(max) )
  26. nchar
  27. varchar (including varchar(max) )
  28. char
  29. varbinary (including varbinary(max) )
  30. binary (lowest)

Reference
https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-ver16

Author

Alma Chang

Check out the Lucid Insights blog

There is a variety of content that may help you to improve your business!