MSSQL - How to do casting or conversion in SQL

on MSSQL

In Microsoft SQL Server, you can use the CAST or CONVERT function to change the data type of a value or expression.

The basic syntax for the CAST function is:

CAST (expression AS data_type)

For example, if you want to convert an integer value to a float, you would use the following query:

SELECT CAST(5 as float)

The basic syntax for the CONVERT function is:

CONVERT (data_type, expression, [style])

For example, if you want to convert a date value to a string in the format "yyyy-mm-dd", you would use the following query:

SELECT CONVERT(varchar, GETDATE(), 23)

You can see the full list of styles here on microsoft.com. The style parameter is optional, it is used to specify the format of the output.

Keep in mind that some data loss can occur during casting and converting, so it is important to check the result and make sure it is what you expect.

What is the difference between CAST and Convert?

The main difference between CAST and CONVERT is the way they handle certain data types and formatting options.

The CAST function:

  • Is used to convert a value or expression of one data type to another data type.
  • Is ANSI SQL compliant, which means it can be used in any SQL database.
  • Does not provide any formatting options for the output, so the result will be displayed in the default format for the data type.

The CONVERT function:

  • Is used to convert a value or expression of one data type to another data type.
    Is specific to Microsoft SQL Server, so it may not be available in other SQL databases.
  • Provides several formatting options for the output, such as different date and time styles, and can be used to format the output in a specific way.
  • Both CAST and CONVERT can be used to convert between most data types in SQL Server, such as int, float, varchar, date, and time. However, CONVERT provides more flexibility and formatting options for some data types, such as date and time.

In summary, CAST is more basic and it is used to change data types, CONVERT provides additional formatting options for some data types and is a Microsoft SQL Server specific function.

That is all

I hope you found this helpful, if you did - or did not - please leave a comment down below, I always enjoy feedback :)