SQL CONVERT

Use CONVERT:

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

SELECT CONVERT (varchar, getdate (), 120)
2004-09-12 11:06:08

select replace (replace (replace (CONVERT (varchar, getdate (), 120), / ‘- /’, / ‘/’), / ‘/’, / ‘/’), / ‘:/’, / ‘/ ‘)
20040912110608

SELECT CONVERT (varchar (12), getdate (), 111)
2004/09/12

SELECT CONVERT (varchar (12), getdate (), 112)
20040912

SELECT CONVERT (varchar (12), getdate (), 102)
2004.09.12

SELECT CONVERT (varchar (12), getdate (), 101)
09/12/2004

SELECT CONVERT (varchar (12), getdate (), 103)
12/09/2004

SELECT CONVERT (varchar (12), getdate (), 104)
12.09.2004

SELECT CONVERT (varchar (12), getdate (), 105)
12-09-2004

SELECT CONVERT (varchar (12), getdate (), 106)
12092004

SELECT CONVERT (varchar (12), getdate (), 107)
0912, 2004

SELECT CONVERT (varchar (12), getdate (), 108)
11:06:08

SELECT CONVERT (varchar (12), getdate (), 109)
091220041

SELECT CONVERT (varchar (12), getdate (), 110)
09-12-2004

SELECT CONVERT (varchar (12), getdate (), 113)
120920041

SELECT CONVERT (varchar (12), getdate (), 114)
11:06:08.177

Explicitly convert the expression of a data type to another data type. CAST and CONVERT provide similar functionality.
The syntax to use CAST:
CAST (expression AS data_type)
Use CONVERT:
CONVERT (data_type [(length)], expression [, style])
Parameter
expression
Is any valid Microsoft ® SQL Server ™ expression. For more information, see the expression.
data_type
Provided by the target system data types, including bigint and sql_variant. You can not use a user-defined data type. For more information about available data types, see Data Types.
length
nchar, nvarchar, char, varchar, binary, or varbinary data types of optional parameters.
style
Date format style to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types); string formatting styles to float, real, money, or smallmoney data is converted to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).
SQL Server supports the use of the data format in the the Kuwait algorithm Arab style.
In the table, the two left-hand column indicates the datetime or smalldatetime conversion to character data style value. To the style value plus 100 obtained including digital four years of the century (yyyy).
Without century bit (yy) with Century Digital (yyyy)
Standard input / output **
– 0 or 100 (*) default value mon dd yyyy hh: MIAM (or PM)
1101 United States mm / dd / yyyy
2 102 ANSI yy.mm.dd
3103 British / French dd / mm / yy
4104 Germany dd.mm.yy
5105 Italy dd-mm-yy
6 106 – dd mon yy
107 – mon dd, yy
8 108 – hh: mm: ss
– 9 or 109 (*) Default + milliseconds mon dd yyyy hh: mi: ss: mmmAM (or PM)
10110 United States mm-dd-yy
11 111 Japan yy / mm / dd
12 112 ISO yymmdd
– 13 or 113 (*) Europe default + milliseconds dd mon yyyy hh: mm: ss: mmm (24h)
14 114 – hh: mi: ss: mmm (24h)
– 20 or 120 (*) ODBC specification yyyy-mm-dd hh: mm: ss [. Fff]
– 21 or 121 (*) ODBC specification (with milliseconds) yyyy-mm-dd hh: mm: ss [fff]
– 126 (***) ISO8601 yyyy-mm-dd Thh: mm: ss: mmm (no spaces)
– 130 * Kuwaiti dd mon yyyy hh: mi: ss: mmmAM
– 131 * Kuwait
The default values ??(style 0 or 100,9 or 109, or 113, or 120,21 or 121) always return the century (yyyy).
** When converting to datetime input; when converted to a character data output.
*** Dedicated to XML. For conversion from datetime or smalldatetime to character data, the output format as shown in the table. For conversion from float, money or smallmoney to character data, the output is equivalent to style 2. Conversion from real to character data, the output is equivalent to style 1.

Important By default, SQL Server 2049 interpret two-digit year cutoff year. That is, the two-digit year 49 is interpreted as 2049, while the two-digit year 50 is interpreted as 1950. Many client applications (such as those based on the OLE Automation object client applications) use 2030 as the cutoff year. SQL Server provides a configuration option (the two-digit year cutoff “), in order to change the cutoff year used by SQL Server and the date of consistency. However, the safest way is to specify the four-digit year.

When converted from smalldatetime to character data, the style that contains the seconds or milliseconds show zeros in these locations. When you convert from datetime or smalldatetime values ??by using the appropriate char or varchar data type length to truncate unwanted date part.
The following table shows the style values ??from float or real to character data.
Value output
0 (default) up to 6 digits. Need to use scientific notation.
1 is always an 8-bit value. Always use in scientific notation.
2 is always 16-bit value. Always use in scientific notation.

In the following table, the left column represents money or smallmoney conversion the style value when the character data.
Value output
0 (default) the left side of the decimal point every three digits separated by commas, and two digits to the right of the decimal point, for example, 4235.98.
1 the left of the decimal point between every three digits separated by commas, and two digits to the right of the decimal point, for example, 3,510.92.
2 the left of the decimal point between every three digits separated by commas, to take a four-digit right of the decimal point, for example, 4235.9819.

Return Types Returns the same value with data type 0.
Notes implicit conversion refers to the conversion of those who do not specify the CAST or CONVERT function. Explicit conversions are those specified conversion function required CAST (CONVERT). The chart below shows the explicit and implicit conversion of all data types can be used for the SQL Server system, these data types, including bigint and sql_variant.

Unicode data always use the even-bit bytes, so when the conversion between binary, or varbinary data types and Unicode data types supported prompt. For example, this conversion does not return the hexadecimal value of 41 but returns 4100 hexadecimal value: SELECT CAST (CAST (0x41 as nvarchar) AS varbinary)

Does not support automatic data type conversion of text and image data types. Explicit conversion of text data to character data, the dominant image data converted to binary or varbinary data, but the maximum length of 8000. If you try to make an incorrect conversion (for example, will contain alphabetic characters expression conversion as int), SQL Server will generate an error message.
When the output of CAST or CONVERT string and input is a string, the output and input have the same collation and collation label. If the input is not a string, then the output using the default collation of the database and force a default collation label. For more information, please see collation priority.
To assign a different collation to the output, set the COLLATE clause applied to the result expression of the CAST or CONVERT function. For example:
SELECT CAST (” abc” AS varchar (5)) COLLATE French_CS_AS
There is no implicit conversion on assignment from sql_variant data type, but there are converted to sql_variant implicit conversion.
Character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) is converted to the expression of different data types, the data may be truncated to display only a subset, or can not be displayed because the result is too short and return an error. In addition to the following table shows the conversion, the conversion to char, varchar, nchar, nvarchar, binary, and varbinary are truncated.
varchar *
nchar E
nvarchar E
money, smallmoney, numeric, decimal, float or real char E
varchar E
nchar E
nvarchar E

* Results length is too short not be displayed.
E can not be displayed because the result length is too short and return an error.
Microsoft SQL Server only round-trip conversion (ie, conversion from the original data type and then return to the original data type) to produce the same value in each version. The following example shows round-trip conversion:
DECLARE @ myval decimal (5, 2) SET @ myval = 193.57SELECT CAST (CAST (@ myval AS varbinary (20)) AS decimal (10,5)) – Or, using CONVERTSELECT CONVERT (decimal (10,5), CONVERT (varbinary (20), @ myval))
For example, do not try to construct binary values ??and convert them to the data types of the numeric data type category.SQL Server does not guarantee the decimal or numeric data type conversion of the results of the binary versions of SQL Server.
The following example shows the result of an expression can not be displayed due to a short.
USE pubsSELECT SUBSTRING (title, 1, 25) AS Title, CAST (ytd_sales AS char (2)) FROM titlesWHERE type =” trad_cook”
Here is the result set:
Title ————————— Onions, Leeks, and Garlic * Fifty Years in Buckingham * Sushi, Anyone? * (3 row (s ) affected)
When the data type conversion with a different number of decimal places, the value will be truncated to the most accurate digital. For example, the results of the SELECT CAST (10.6496 AS int) is 10.
Conversion, if the target data type of decimal places is less than the number of decimal places of the source data type, will have to convert the value to be rounded. For example, CAST (10.3496847 AS money) is $ 10.3497.
When non-numeric type char, nchar, varchar, or nvarchar data conversion for int, Float, numeric or decimal, SQL Server will return an error message. When an empty string (“”) is converted to a numeric or decimal, SQL Server will return an error message.
Use the binary string data to binary, or varbinary data to character data and specify the value of the odd bit behind the x, SQL Server behind the x Add 0 (zero) to become even bit value.
The binary data that contains from 0 to 9 and from A to F (or characters from a to f), each of the two characters as a group.Binary string must begin with 0x. For example, to enter the FF, please type 0xFF. The maximum value is a binary value of 8000 bytes, the maximum value of each byte are FF. Binary data types can not be used for hexadecimal data, but for the bit pattern. Hexadecimal numbers stored as binary data conversion and calculation results can not guarantee its accuracy.
When you specify the length of the binary data type, each of the two characters are counted as a unit length. Length 10 shows the input 10 pairs of groups of characters.
Represented by 0x empty binary string can be saved as binary data.
Example
A. CAST and CONVERT
Each example will retrieve the title (the end of the first digit of the current sales of these books), and these books ytd_sales converted to char (20).
– Use CAST.USE pubsGOSELECT SUBSTRING (title, 1, 30) AS Title, ytd_salesFROM titlesWHERE CAST (ytd_sales AS char (20)) LIKE” 3%” GO – Use CONVERT.USE pubsGOSELECT SUBSTRING (title, 1, 30) AS Title, ytd_salesFROM titlesWHERE CONVERT (char (20), ytd_sales) LIKE” 3%” GO
Here is either query result set:
Title ytd_sales —————————————– Cooking with Computers: Surrep 3876 Computer Phobic AND Non-Phobic 375 Emotional Security: A New Algo 3336 Onions, Leeks, and Garlic: Coo 375 (4 row (s) affected)
B. Use CAST with arithmetic operators
The following example by dividing the total-date sales (ytd_sales) with the price of each book (price) separate series (Copies).Rounded to the nearest integer, the result will be converted to an int data type.
USE pubsGOSELECT CAST (ROUND (ytd_sales / price, 0) AS int) AS” Copies” FROM titlesGO
Here is the result set:
Copies —— 205 324 6262 205 102 7440 NULL 383 205 NULL 17 187 16 204 418 18 1263 273 (18 row (s) affected)
C. Using CAST series following example uses the CAST data type conversion functions to non-character, non-binary expression in series.
USE pubsGOSELECT” The price is” + CAST (price AS varchar (12)) FROM titlesWHERE price> 10.00GO
Here is the result set:
—————— The price is 19.99 The price is 11.95 The price is 19.99 The price is 19.99 The price is 22.95 The price is 20.00 The price is 21.59 The price is 10.95 The price is 19.99 The price is 20.95 The price is 11.95 The price is 14.99 (12 row (s) affected)
D. CAST for more easy-to-read text following example uses CAST in the select list title column converted to char (50) column, so that the results will be more readable.
USE pubsGOSELECT CAST (title AS char (50)), ytd_salesFROM titlesWHERE type =” trad_cook” GO
Here is the result set:
ytd_sales ————————————————- ———- Onions, Leeks, and Garlic: Cooking Secrets of the 375Fifty Years in Buckingham Palace Kitchens 15096Sushi, Anyone? 4095 (3 row (s) affected)
E. Use with LIKE clause CAST
The following example int column (ytd_sales column) is converted to char (20) column in order to use the LIKE clause.
USE pubsGOSELECT title,

ytd_salesFROM titlesWHERE CAST (ytd_sales AS char (20)) LIKE” 15%” AND type =” trad_cook” GO

Here is the result set:
title ytd_sales ———————————————— ————