"SQL Server Series" cast and convert

1 overview

This article focuses on the two functions involved in type conversion in SQL Server: Cast and convert.

2 Specific content

2.1 CAST

(1) Role: An expression that converts an expression of one data type to another data type.

(2) Definition:

1 CAST  as Data_type)

Parameters:

Expression: Any valid expression that can be converted to data_type ;

Data_type: The target data type provided by the system. The type must be a primitive (scalar) type. Use data_type depends on the query space. If you use EntityCommand to execute a query, the data type is the type defined in the conceptual model.

Return Value::

Returns the data_type same value as the same.

(3) Example

1 SELECT CAST(' .'  as INT)-- .2 3 SELECT CAST( .  as INT)-- .4 5 SELECT CAST(2017.3  as INT)-- .6 7 SELECT CAST('2017.3'  as INT)--msg 245, Level 16, State 1, line 1th, fails when the varchar value ' 2017.3 ' is converted to a data type int. 8 9 SELECT CAST('2017.3'  as decimal)-- .Ten  One SELECT CAST('2017.3'  as decimal(8,2))--2017.30

2.2 CONVERT

(1) Role: An expression that converts an expression of one data type to another data type.

(2) Definition:

1 CONVERT [][] )

Parameters:

Expression : Any valid expressions;

Data_type: Target data type. This includes XML,bigint, and sql_variant. Cannot use alias data type;

Length: An optional integer specifying the length of the target data type. The default value is 30;

Style: Specifies how the CONVERT function converts an integer expression of expression. If the style is NULL, NULL is returned. The scope is determined by the data_type. For more information, see the Remarks section;

Return Value:

Returns the expression converted to Data_type .

(3) Example

1 SELECT CONVERT(INT,' .')-- .2  3 SELECT CONVERT(INT,2017.3)-- .4 5 SELECT CONVERT(INT,'2017.3')--msg 245, Level 16, State 1, line 1th, fails when the varchar value ' 2017.3 ' is converted to a data type int. 6 7 SELECT CONVERT(decimal,'2017.3')-- .8 9 SELECT CONVERT(decimal(8,2),'2017.3')--2017.30

2.3 Cast and convert comparison

(1) Cast is generally easier to use, the advantage of convert can be formatted date and value;

(2) Convert is generally used for date and time values, conversion between decimals, cast is generally used for fractional to numeric and character type;

(3) Converk display conversion, cast is a forced conversion;
(4) Example

1 Select CONVERT(varchar,getdate(), -)--2017-08-13 03:56:142  3 Select Replace(Replace(Replace(CONVERT(varchar,getdate(), -),'-',"'),' ',"'),':',"')--201708130356274  5 Select CONVERT(varchar( A) ,getdate(),111)--2017/08/136  7 Select CONVERT(varchar( A) ,getdate(), the)--201708138  9 Select CONVERT(varchar( A) ,getdate(),102)--2017.08.13Ten   One Select CONVERT(varchar( A) ,getdate(),101)--08/13/2017 A   - Select CONVERT(varchar( A) ,getdate(),103)--13/08/2017 -   the Select CONVERT(varchar( A) ,getdate(),104)--13.08.2017 -   - Select CONVERT(varchar( A) ,getdate(), the)--13-08-2017 -   + Select CONVERT(varchar( A) ,getdate(),106)-- from -   + Select CONVERT(varchar( A) ,getdate(),107)--, A   at Select CONVERT(varchar( A) ,getdate(),108)--03:57:39 -    - Select CONVERT(varchar( A) ,getdate(),109)--Geneva -   - Select CONVERT(varchar( A) ,getdate(), the)--08-13-2017 -   in Select CONVERT(varchar( A) ,getdate(),113)--0 -   to Select CONVERT(varchar( A) ,getdate(), the)--03:58:08:200 +   - Select getdate()--2017-08-13 03:58:48.660 the   * Select Convert(Char(8),getdate(), the)--20170813 $  Panax Notoginseng Select Convert(Char(8),getdate(),108)--03:58:48 -   the Select Convert(Char(8),getdate(), the)--20170813

Reference table:

3 references

"01" https://msdn.microsoft.com/zh-cn/library/bb399172 (v=vs.110). aspx

"02" https://technet.microsoft.com/zh-cn/library/ms187928 (v=sql.105). aspx

4 Copyrights

• Thank you for your reading, if there are shortcomings, welcome advice, common learning and common progress.
• Bo main website: http://www.cnblogs.com/wangjiming/.
• A very small number of articles using reading, reference, reference, copying, copying and pasting into a variety of ways, most of the original.
• If you like, please recommend, if you have new ideas, welcome, email: [Email protected].
• The blog can be reproduced, but must be well-known from the blog source.

"SQL Server Series" cast and convert