SQL Server Convert() Function

One of the ways to convert a given datetime value to a string is by using the convert() function. The syntax is as shown:

The function parameters are expressed below:

1. data_type – the target data type.
2. Expression – any valid expression
3. length – optional integer defining the length of the target data type.
4. style – an integer expression that defines how the function translates the provided expression.

The function returns the input expression converted into the target data type.

Therefore, to convert a datetime to a string, we can use the syntax as:

The table below shows the valid styles and equivalent format of the datetime value after conversion to a string type.

Example

The following example demonstrates how to convert a datetime to a string.

In the example above, we start by declaring a scalar variable called obj. This holds the datetime value we wish to convert.

Finally, we call the convert function and pass the target data type as varchar. This should return the style for the specified datetime as:

Example 2

To convert the datetime object to mm/dd/yyyy format. Set the style as 1.

Resulting output:

Example 3

To return the datetime value in the dd.mm.yyyy format, we can set the style as 4.

Output:

Example 4

To convert datetime to string in the format of hh:mi:ss, run the code:

Return value:

Conclusion

This post explored the basics of converting a given datetime value to a string in various formats. You can check the multiple formats and the corresponding styles in the above table.