How to use CONVERT function in SQL Server?
CONVERT Syntax
The CONVERT() function converts a value into a specified data type in SQL Server. It’s quite similar to CAST() function but only applies to SQL Server, while CAST() is a ANSI standard function and used across multiple RDBMS.
Here is the generic syntax for CONVERT() function:
CONVERT(data_type[( length )], expression [, style])
The function convert the input value from expression to the target data_type, using the specified style.
-
For the date and time styles, see in the Date format cheatsheet.
-
For the float and real styles:
| Value | Output |
|---|---|
| 0 (default) | A maximum of 6 digits. Use in scientific notation, when appropriate. |
| 1 | Always 8 digits. Always use in scientific notation. |
| 2 | Always 16 digits. Always use in scientific notation. |
| 3 | Always 17 digits. Use for lossless conversion. With this style, every distinct float or real value is guaranteed to convert to a distinct character string. |
| 126, 128, 129 | Included for legacy reasons; a future release could deprecate these values. |
Example:
Convert a number to a string:
SELECT CONVERT(VARCHAR, 2492);
Or convert a date time value to a string:
SELECT CONVERT(VARCHAR, GETDATE(), 3);
Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL and many other databases simultaneously using an intuitive and powerful graphical interface.
Not on Mac? Download TablePlus for Windows.
Need a quick edit on the go? Download for iOS
