SQL Server Convert Function + Examples

by
SQL Server Convert Function

In this SQL Server tutorial, we will discuss SQL Server Convert() function, Different SQL Server Convert Examples, and cover the below topic.

  • SQL Server Convert
  • SQL Server Convert string to date
  • SQL Server Convert DateTime
  • SQL Server Convert DateTime to date
  • SQL Server Convert row to column
  • SQL Server Convert int to string / varchar
  • SQL Server Convert date to YYYYMMDD
  • SQL Server Convert UTC to local time
  • SQL Server Convert timestamp to date

For all the examples, I have used SQL server 2019 and SQL Server management studio.

SQL Server Convert Function

In SQL Server, Convert() is a function used to convert an expression from one data type to an expression of another data type. And if the conversion fails, it will return an error. Otherwise, it will return the converted data type expression. It has the following syntax.

CONVERT( data_type [ ( length ) ] , expression [ , style ] )
  • In the above syntax, the data_type argument is used to define the resulting data type of the expression that a user wants in the end. And it supports multiple data types such as int, bigint, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, DateTime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, etc.
  • The length is an optional parameter, which accepts integer values. And it is used to define the length of the resulting data type. The default value of length is 30.
  • The expression in the above syntax is used to define the original expression whose data type a user wants to convert.
  • The style is an optional parameter, which accepts integer values. And it is used to define how the Convert() function will translate the given expression.

Read: How to create functions in SQL Server

Example For SQL Server Convert()

SELECT CONVERT(int, 99.65) AS Result
convert function in sql server
Example For SQL Server Convert() function

So in the above example, we have defined our input expression as “99.65” which is a float type expression and in the data_type argument, we are defining “int“. So by using the convert function query we are changing the data type of “99.65” from float to integer. And after successful execution, it will return the following result.

SQL Server Convert
Final Output

SQL Server Convert string to date

In SQL Server, to convert a string expression to a date expression there are 3 different conversion functions available. We are going to discuss each of the following in detail with examples.

  1. Cast() Function
  2. Convert() Function
  3. Parse() Function

Converting string to date using Cast() function

The Cast() function is one of the basic conversion functions available in SQL Server which is used to convert expressions from one data type to another. It has the following syntax

CAST( expression AS data_type [ ( length ) ] )

Example

SELECT CAST('11/06/2021' as date) AS StringToDate
SQL Server Convert string to date
Converting string to date using Cast()

In the above example, we have defined our input expression as ’11/06/21′ and we have also defined the data_type as a date. And there is no need to define the length in case of date. So the above query will convert the ’11/06/21′ expression to date and will return the following output.

SQL Server Convert string to date
Query Result

Read Arithmetic operators in SQL Server

Converting string to date using Convert() function

The Convert() is another conversion function available in SQL Server. It is more efficient than the Cast() function because a user can define the conversion style using it. And it is being described in detail at the starting of this post.

Now while converting a string to date using the convert() function, it is important to specify the style type in the query. So the correct conversion can take place.

Example

SELECT CONVERT(DATE,'11/06/2021',103) AS StringToDate
string to date using covert in sql server
String to Date using Covert() in SQL Server

So in the example first we have defined the data_type as DATE, then we have defined the input string expression as ’11/06/2021′. And in the last, we have defined the style code as 103 which is used to specify the date format as ‘DD/MM/YYYYY’.

After successfully query execution, it will return the following output.

string to date using convert sql server
Final Output

Converting string to date using Parse() function

The Parse() is also a conversion function available in SQL Server, which is used to translate the input string either to a numeric value or a date/time expression. Now to transform the string value, the PARSE() function uses the Common Language Runtime (CLR). And if there is no CLR installed in the server, the PARSE() function will return an error.

The Parse() function has the following syntax.

PARSE( string_value AS data_type [ USING culture ] )
  • The string_value is used to represent a nvarchar(4000) value that a user wants to convert to a resulting data type expression.
  • The PARSE() function will raise an error if string_value is not a valid representation of the requested data type.
  • The data_type parameter is used to specify the resulting data type that a user wants.
  • The culture parameter in the above syntax is an optional string parameter that is used to specify the culture in which string_value is formatted. And if the culture is not specified, it will use the current session language.

Example

SELECT PARSE('11/06/2021' as date) AS StingToDate
string to date using parse in sql server
String to Date using Parse() in SQL Server

So in the above example, first we have defined the string_value as ‘11/06/2021‘, then we have defined the data_type as the date and we are not using the culture option in this example. In the end, it will return the following result.

string to date using parse sql server
Final Result

SQL Server Convert DateTime

In SQL Server there are various conversion functions available related to Datetime such as Cast(), Convert(), Format() etc. Out of all these, Convert() function offers various options related to Datetime format using the styles parameter. The styles parameter in Convert() function accepts integer values and it is used to specify the format of conversion.

Styles values related to Datetime conversion are explained below.

Without centuryWith centuryInput / Output
0100mon dd yyyy hh:miAM/PM
11011 = mm/dd/yy
101 = mm/dd/yyyy
21022 = yy.mm.dd
102 = yyyy.mm.dd
31033 = dd/mm/yy
103 = dd/mm/yyyy
41044 = dd.mm.yy
104 = dd.mm.yyyy
5105 5 = dd-mm-yy
105 = dd-mm-yyyy
61066 = dd mon yy
106 = dd mon yyyy
71077 = Mon dd, yy
107 = Mon dd, yyyy
8108hh:mm:ss
9109mon dd yyyy hh:mi:ss:mmmAM (or PM)
1011010 = mm-dd-yy
110 = mm-dd-yyyy
1111111 = yy/mm/dd
111 = yyyy/mm/dd
12112 12 = yymmdd
112 = yyyymmdd
13113dd mon yyyy hh:mi:ss:mmm
14114hh:mi:ss:mmm
20120yyyy-mm-dd hh:mi:ss
21121yyyy-mm-dd hh:mi:ss.mmm
 126yyyy-mm-ddThh:mi:ss.mmm
 127yyyy-mm-ddThh:mi:ss.mmmZ
 130dd mon yyyy hh:mi:ss:mmmAM
 131dd/mm/yy hh:mi:ss:mmmAM
SQL Server Convert DateTime

Read: SQL Server Substring Function [9 Examples]

SQL Server Convert DateTime to date

Before converting a DateTime value to a date value let’s first understand both of these expressions. The DateTime expression contains both date details as well as timing details. And a simple date expression contains the date details only.

In SQL Server to get current DateTime, we can use the GETDATE() function. It returns the current database system date and time value. And the value is generated from the operating system of the computer running SQL Server.

Syntax

GETDATE()

Example

SELECT GETDATE() AS Date
getdate query in sql server
GETDATE() Query in SQL Server

Now to convert a DateTime expression to a date expression in SQL Server we can use 3 different functions which are

  • Convert() Function
  • Try_Convert() Function
  • Cast() Function

And we will understand each function with the help of an example.

Using Convert() to convert datetime to date

In this, we are going to use the Convert() function to convert Datetime to a date value.

CONVERT( DATE, expression )

To convert a Datetime value to the correct date value we can follow the above syntax. And we only need to specify a valid Datetime expression in the expression parameter.

Example

SELECT CONVERT(DATE, GETDATE()) AS Date;
using convert function to translate datetime to date
Using Convert() function to translate Datetime to date in SQL Server

In this example, we are using the GETDATE() function to get the current system Datetime value and we are using it as an input expression. And we are also defining the data_type as DATE in the query.

Using Try_Convert() to convert datetime to date

The Try_Convert() function in SQL Server is pretty much similar to the Convert() function which is used to transform a value of one data type to another data type. The main difference between Try_Convert() and Convert() functions is that the Try_Convert function will return NULL if the conversion of a data type fails. Whereas the Convert() function will raise an error.

The Try_Convert() function has the following syntax.

TRY_CONVERT( data_type [ ( length ) ], expression [, style ] )

Example

SELECT TRY_CONVERT(DATE, GETDATE()) AS Date;
using try_convert function to translate datetime to date
Using Try_Convert() function to translate Datetime to date

Using Cast() to convert datetime to date

In this, we are going to use the CAST() function to convert Datetime to a date value.

CAST(expression AS DATE)

Now to convert a Datetime value to the correct date expression using CAST(), we can follow the above syntax. And we only need to specify a valid Datetime expression in the expression parameter.

Example

using cast function to translate datetime to date in sql server
Using Cast() function to translate Datetime to date

SQL Server Convert row to column

In SQL Server to convert a row to a column, we can use the PIVOT operator. A PIVOT in SQL Server is a relational operator that is used to change one table-valued expression to another. It is used to transfer row-level data to a column. And it has the following syntax.

SELECT <non-pivoted column>,  
    [first pivoted column] AS <column name>,   
    ...  
    [last pivoted column] AS <column name>  
FROM  
    (<SELECT query that produces the data>)   
    AS <alias for the source query>  
PIVOT  
(  
    <aggregation function>(<column being aggregated>)  
FOR   
[<column that contains the values that will become column headers>]   
    IN ( [first pivoted column], [second pivoted column],  
    ... [last pivoted column])  
) AS <alias for the pivot table>  
<optional ORDER BY clause>;

Let’s understand the implementation of the PIVOT operator with the help of an example. For demonstration, we have created the following employee sales table. And now we will try to convert rows of the table to columns.

SQL Server Convert row to column
Sample Employee Sales table in SQL Server

The above table contains yearly sales information of employees working in an organization. Now we will change the rows of the table to columns using the PIVOT operator. For this, we are going to use the following query.

SELECT [Year], Lin,Ross,Sandy FROM   
(SELECT Name, [Year] , Sales FROM Employee )Tab1  
PIVOT  
(  
SUM(Sales) FOR Name IN (Lin,Ross,Sandy)) AS Tab2  
ORDER BY [Tab2].[Year]

In the above query, we first select new columns as Year, Lin, Ross, and Sandy. After that, we are combining the sales of each employee yearly. In the end, we are ordering the table values according to year. And it will return the following output.

pivot operator example in sql server
Final Output

SQL Server Convert int to string/varchar

In SQL Server, there are mainly 2 functions available that we can use to convert an integer value to a string expression. And we will discuss both functions with help of an example.

Using Cast() to convert int to string

SELECT CAST(43210 as varchar(10)) as Num1

In the above query, we use the Cast() conversion function to convert an integer value to a varchar expression. For this first, we have used 43210 as an integer value and then we have defined the resulting data type as varchar. And after successful execution, it will return the following value.

cast function to convert int to string
Final output

Using Convert() to convert int to string

SELECT CONVERT(varchar(10), 43210) as Num2

Now, we using the Convert() function to convert an integer value to a varchar expression. In the above query we are converting 43210 to an varchar expression. And it will return the following result.

convert function to convert int to string
Query Result

Read: SQL Server convert integer to string

SQL Server Convert date to YYYYMMDD

In SQL Server, we can also use the Year, Month, Day format ‘YYYYMMDD’ as an output or as a filter for our results. It is a more compact method of displaying the Date in a sortable format. In this, the delimiter between the year, month, and the day is not required. It is an excellent alternative when a uniform standard for a date is required.

In SQL Server, there are 2 functions available through which we can easily convert a standard date value to “YYYYMMDD” format.

Using Format() function to convert date to YYYYMMDD

The Format function in SQL Server is used to change the format of a given value according to the newly specified format. And it is mainly used to format numeric and date/time values. It has the following syntax.

FORMAT( value, format [, culture ] )

The Format function has 2 required and 1 optional parameter. The first required parameter is a value that is used to define the actual expression value that a user wants to format. The second required parameter is a format that is used to specify the resulting format that a user wants. And the last is an optional parameter, culture which is used to specify the culture.

Example

SELECT FORMAT(GETDATE(), 'yyyyMMdd') AS NewFormat

In this example, we are using the GETDATE() function to get the current system date value as we are using it as an input value. After this, we are specifying the required format as ‘yyyyMMdd’. In the end, it will return the following output.

SQL Server Convert date to YYYYMMDD
Format function result

Using Convert() function to convert date to YYYYMMDD

SELECT CONVERT(varchar, GETDATE(),112) AS NewFormat

In the above query, we are using the Convert() function to change the date format value to YYYYMMDD. For this, we are converting the date value to a varchar expression and we are also defining the style code as 112 which is used to define the resulting format as YYYYMMDD. And the query will return the following output.

using convert function to translate date to yyyymmdd
Final Output

SQL Server Convert UTC to local time

In SQL Server 2016 or later versions, we can easily convert the UTC time zone to a local time zone. The SQL Server stores the basic information related to all the supported time zones in a table named “sys.time_zone_info“. We can easily list all the supported time zones using the query. 

select * from sys.time_zone_info;

The above query will return a table containing names of all the supported time zones in SQL Server and we can use these time zone names to convert UTC zone to any local time zone supported in SQL Server. This table contains more than 100 time zones

SQL Server Convert UTC to local time
Supported time zones in SQL Server

Now as we get all the supported time zone names in SQL Server we can easily use the following query to convert UTC zone timestamp to local time zone.

SELECT GETUTCDATE() AS utc_time_zone, 
CAST(GETUTCDATE() as datetime) AT TIME ZONE 'US Eastern Standard Time' 
AS local_time_zone

In the above query, first, we are using the GETUTCDATE() function to get the current database system UTC date and time, in a ‘YYYY-MM-DD hh:mm:ss.mmm’ format. And then we are using the Cast() function to change the data type to Datetime. In the last, we are defining the required time zone as ‘US Eastern Standard Time’ But we also define any time zone which is supported by SQL Server instance. The above query will return the following result.

changing utc to local time zone in sql server
SQL Server Convert UTC to local time

SQL Server Convert timestamp to date

So, unfortunately, there is no way through which we can convert a timestamp format to only the date format as there is no relation between any of them. And we can also refer to this stack overflow site which will give more information related to it.

You may like the following sql server articles:

So in this SQL Server tutorial, we have learned SQL Server Convert() function,  Different SQL Server Convert Examples, and cover the below topic.

  • SQL Server Convert
  • SQL Server Convert string to date
  • SQL Server Convert DateTime
  • SQL Server Convert DateTime to date
  • SQL Server Convert row to column
  • SQL Server Convert int to string / varchar
  • SQL Server Convert date to YYYYMMDD
  • SQL Server Convert UTC to local time
  • SQL Server Convert timestamp to date