CONVERSION FUNCTION IN SQL SERVER 2014 || CAST AND CONVERT FUNCTION IN SQL SERVER 2014

This video will provide you the details of each Conversion functions in SQL Server 2014. I'll be walking through each and every kinds of build in function which are available in SQL Server in my next videos.

Conversion Functions :-
CAST and CONVERT (Transact-SQL)
Converts an expression of one data type to another.

 Syntax
-- Syntax for CAST:
CAST ( expression AS data_type [ ( length ) ] )

-- Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )


Implicit Conversion Explicit Conversion
Implicit Conversion is done automatically Explicit Conversion is done programmatically.
In Implicit conversion, no data loss take place during the data conversion. In explicit conversion, data loss may or may not be take place during data conversion. Hence there is a risk of information loss.
No possibility of throwing exception during the conversion and therefore is called type safe. It might throw error if tried to do without type casting.
Implicit conversion do not require any special syntax. Explicit conversion do require cast operator to perform conversion.
Example : Example :
Conversion of smaller number to larger number is implicit conversion. Conversion of larger number to smaller number is explicit conversion.

 Arguments
expression
Is any valid expression.
data_type
Is the target data type. This includes xml, bigint, and sql_variant. Alias data types cannot be used.
length
Is an optional integer that specifies the length of the target data type. The default value is 30.
style
Is an integer expression that specifies how the CONVERT function is to translate expression. If style is NULL, NULL is returned. The range is determined by data_type. For more information, see the Remarks section.
 Return Types
Returns expression translated to data_type.

 Remarks
• Date and Time Styles
When expression is a date or time data type, style can be one of the values shown in the following table. Other values are processed as 0. . Beginning with SQL Server 2012, the only styles that are supported when converting from date and time types to datetimeoffset are 0 or 1. All other conversion styles return error 9809.
SQL Server supports the date format in Arabic style by using the Kuwaiti algorithm.
Without century (yy) (1) With century (yyyy) Standard Input/Output (3)
- 0 or 100 (1,2) Default for datetime and smalldatetime mon dd yyyy hh:miAM (or PM)
1 101 U.S. 1 = mm/dd/yy

101 = mm/dd/yyyy

3 103 British/French 3 = dd/mm/yy

103 = 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)

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'.

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