There are two functions for converting SQL data types from one to another. They are CAST and CONVERT. The second function is the best solution for converting datetime to format you need.

Syntax for CAST:

CAST ( expression AS data_type [ ( length ) ] )

 

Syntax for CONVERT:

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

 

Function CONVERT is especially good to use for datetime types, but you can use it and for other ones.

For examples, we’ll declare and set values for next variables:

DECLARE @NUM FLOAT = 124.5
DECLARE @dollar VARCHAR(1) = '$'
DECLARE @date DATE  = '03/07/2015'

Example of CAST:

If we write this SQL: SELECT @dollar + @NUM, we’ll get the error like this:

 

SELECT @dollar + CAST(@NUM as varchar(5))

The result is:

You can use AS for creating alias for your result column, for example:

SELECT (@dollar + CAST(@NUM as varchar(5))) as dollarstring

The result with alias is on Figure 3. Result column is called "dollarstring":

Using CAST you can convert datetime type, for example, to varchar, but you can’t modify it’s style:

SELECT CAST(@date as varchar(13)) + @dollar

This will return "2015-03-07$"

But using CONVERT function you can not only convert type of column, but and to change display style of datetime value. For example, if your column data looks like "yyyy-mm-dd", you can easily make it look "dd/mm/yy" or "dd/mm/yyyy".  You can set it in "style" value – it’s an integer value that specifies how the CONVERT function should translate expression.

Let’s see the result of this SQL command (Figure 5):

SELECT CONVERT(varchar(10), @date, 102)

If I use "02" instead of "102" (SELECT CONVERT(varchar(10), @date, 02)), the result is:

Of course, there is a table of "style" values. It is in https://msdn.microsoft.com/ru-ru/library/ms187928(v=sql.120).aspx, but below I write down main values. If in the first column you can see a digit, you can use as with "1" (with century), so and without "1" (without century).

Without century (yy) (1)	With century (yyyy)	Standard	Input/Output (3)
-	0 or 100 (1, 2)	Default	mon dd yyyy hh:miAM (or PM)
1	101	U.S.	1 = mm/dd/yy 101 = mm/dd/yyyy
2	102	ANSI	2 = yy.mm.dd 102 = yyyy.mm.dd
3	103	British/French	3 = dd/mm/yy 103 = dd/mm/yyyy
4	104	German	4 = dd.mm.yy 104 = dd.mm.yyyy
5	105	Italian	5 = dd-mm-yy 105 = dd-mm-yyyy
6	106 (1)	-	6 = dd mon yy 106 = dd mon yyyy
7	107 (1)	-	7 = Mon dd, yy 107 = Mon dd, yyyy
8	108	-	hh:mi:ss
-	9 or 109 (1, 2)	Default + milliseconds	mon dd yyyy hh:mi:ss:mmmAM (or PM)
10	110	USA	10 = mm-dd-yy 110 = mm-dd-yyyy
11	111	JAPAN	11 = yy/mm/dd 111 = yyyy/mm/dd
12	112	ISO	12 = yymmdd 112 = yyyymmdd
-	13 or 113 (1, 2)	Europe default + milliseconds	dd mon yyyy hh:mi:ss:mmm(24h)
14	114	-	hh:mi:ss:mmm(24h)
-	20 or 120 (2)	ODBC canonical	yyyy-mm-dd hh:mi:ss(24h)
-	21 or 121 (2)	ODBC canonical (with milliseconds)	yyyy-mm-dd hh:mi:ss.mmm(24h)
-	126 (4)	ISO8601	yyyy-mm-ddThh:mi:ss.mmm (no spaces)
			Note
			When the value for milliseconds (mmm) is 0, the millisecond value is not displayed. For example, the value '2012-11-07T18:26:20.000 is displayed as '2012-11-07T18:26:20'.
-	127(6, 7)	ISO8601 with time zone Z.	yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)
			Note
			When the value for milliseconds (mmm) is 0, the milliseconds value is not displayed. For example, the value '2012-11-07T18:26:20.000 is displayed as '2012-11-07T18:26:20'.
-	130 (1, 2)	Hijri (5)	dd mon yyyy hh:mi:ss:mmmAM In this style, mon represents a multi-token Hijri unicode representation of the full month's name. This value will not render correctly on a default US installation of SSMS.
-	131 (2)	Hijri (5)	dd/mm/yyyy hh:mi:ss:mmmAM

 

1 These style values return nondeterministic results. Includes all (yy) (without century) styles and a subset of (yyyy) (with century) styles.

2 The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the century (yyyy).

3 Input when you convert to datetime; output when you convert to character data.

4 Designed for XML use. For conversion from datetime or smalldatetime to character data, the output format is as described in the previous table.

5 Hijri is a calendar system with several variations. SQL Server uses the Kuwaiti algorithm.