Convert image to Data URI string online. Data URI is a method for embedding small images directly in your HTML or CSS code using base64 encoding without the need for additional image files.
-->These functions convert an expression of one data type to another.
Example: Change the input datatype
Cast
Convert
Here is the result set.
Original | int | decimal |
---|---|---|
9.5 | 9 | 9.5000 |
See the examples later in this topic.
Syntax
Arguments
expression
Any valid expression.
Any valid expression.
data_type
The target data type. This includes xml, bigint, and sql_variant. Alias data types cannot be used.
The target data type. This includes xml, bigint, and sql_variant. Alias data types cannot be used.
length
An optional integer that specifies the length of the target data type. The default value is 30.
An optional integer that specifies the length of the target data type. The default value is 30.
style
An integer expression that specifies how the CONVERT function will translate expression. For a style value of NULL, NULL is returned. data_type determines the range.
An integer expression that specifies how the CONVERT function will translate expression. For a style value of NULL, NULL is returned. data_type determines the range.
Return types
Returns expression, translated to data_type.
Date and Time Styles
For a date or time data type expression, style can have one of the values shown in the following table. Other values are processed as 0. Beginning with SQL Server 2012 (11.x), the only styles supported, when converting from date and time types to datetimeoffset, are 0 or 1. All other conversion styles return error 9809.
Note
SQL Server supports the date format, in Arabic style, with 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 |
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) default for time, date, datetime2, and datetimeoffset | yyyy-mm-dd hh:mi:ss.mmm(24h) |
- | 126 (4) | ISO8601 | yyyy-mm-ddThh:mi:ss.mmm (no spaces) Note: For a milliseconds (mmm) value of 0, the millisecond decimal fraction value will not display. For example, the value '2012-11-07T18:26:20.000 displays as '2012-11-07T18:26:20'. |
- | 127(6, 7) | ISO8601 with time zone Z. | yyyy-mm-ddThh:mi:ss.mmmZ (no spaces) Note: For a milliseconds (mmm) value of 0, the millisecond decimal value will not display. For example, the value '2012-11-07T18:26:20.000 will display 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 name. This value does 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 (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, see the previous table for the output format.
5 Hijri is a calendar system with several variations. SQL Server uses the Kuwaiti algorithm.
Important
By default, SQL Server interprets two-digit years based on a cutoff year of 2049. That means that SQL Server interprets the two-digit year 49 as 2049 and the two-digit year 50 as 1950. Many client applications, including those based on Automation objects, use a cutoff year of 2030. SQL Server provides the two digit year cutoff configuration option to change the cutoff year used by SQL Server. This allows for the consistent treatment of dates. We recommend specifying four-digit years.
6 Only supported when casting from character data to datetime or smalldatetime. When casting character data representing only date or only time components to the datetime or smalldatetime data types, the unspecified time component is set to 00:00:00.000, and the unspecified date component is set to 1900-01-01.
7Use the optional time zone indicator Z to make it easier to map XML datetime values that have time zone information to SQL Server datetime values that have no time zone. Z indicates time zone UTC-0. The HH:MM offset, in the + or - direction, indicates other time zones. For example:
2006-12-12T23:45:12-08:00
.When converting smalldatetime to character data, the styles that include seconds or milliseconds show zeros in these positions. When converting from datetime or smalldatetime values, use an appropriate char or varchar data type length to truncate unwanted date parts.
When converting character data to datetimeoffset, using a style that includes a time, a time zone offset is appended to the result.
float and real styles
For a float or realexpression, style can have one of the values shown in the following table. Other values are processed as 0.
Value | Output |
---|---|
0 (default) | A maximum of 6 digits. Use in scientific notation, when appropriate. |
1 | Always 8 digits. Always use in scientific notation. |
2 | Always 16 digits. Always use in scientific notation. |
3 | Always 17 digits. Use for lossless conversion. With this style, every distinct float or real value is guaranteed to convert to a distinct character string. Applies to: Azure SQL Database, and starting in SQL Server 2016 (13.x). |
126, 128, 129 | Included for legacy reasons; a future release could deprecate these values. |
money and smallmoney styles
For a money or smallmoneyexpression, style can have one of the values shown in the following table. Other values are processed as 0.
Value | Output |
---|---|
0 (default) | No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point Example: 4235.98. |
1 | Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point Example: 3,510.92. |
2 | No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point Example: 4235.9819. |
126 | Equivalent to style 2, when converting to char(n) or varchar(n) |
xml styles
For an xmlexpression, style can have one of the values shown in the following table. Other values are processed as 0.
Value | Output |
---|---|
0 (default) | Use default parsing behavior that discards insignificant white space, and does not allow for an internal DTD subset. Note: When converting to the xml data type, SQL Server insignificant white space is handled differently than in XML 1.0. For more information, see Create Instances of XML Data. |
1 | Preserve insignificant white space. This style setting sets the default xml:space handling to match the behavior of xml:space='preserve'. |
2 | Enable limited internal DTD subset processing. If enabled, the server can use the following information that is provided in an internal DTD subset, to perform nonvalidating parse operations. - Defaults for attributes are applied - Internal entity references are resolved and expanded - The DTD content model is checked for syntactical correctness The parser ignores external DTD subsets. Also, it does not evaluate the XML declaration to see whether the standalone attribute has a yes or no value. Instead, it parses the XML instance as a stand-alone document. |
3 | Preserve insignificant white space, and enable limited internal DTD subset processing. |
Binary styles
For a binary(n), char(n), varbinary(n), or varchar(n)expression, style can have one of the values shown in the following table. Style values not listed in the table will return an error.
Value | Output |
---|---|
0 (default) | Translates ASCII characters to binary bytes, or binary bytes to ASCII characters. Each character or byte is converted 1:1. For a binary data_type, the characters 0x are added to the left of the result. |
1, 2 | For a binary data_type, the expression must be a character expression. The expression must have an even number of hexadecimal digits (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F, a, b, c, d, e, f). If the style is set to 1, the must have 0x as the first two characters. If the expression contains an odd number of characters, or if any of the characters is invalid, an error is raised. If the length of the converted expression exceeds the length of the data_type, the result is right truncated. Fixed length data_types larger than the converted result have zeros added to the right of the result. A data_type of type character requires a binary expression. Each binary character is converted into two hexadecimal characters. If the length of the converted expression exceeds the length of the data_type, it will be right truncated. For a fixed size character type data_type, if the length of the converted result is less than its length of the data_type, spaces are added to the right of the converted expression, to maintain an even number of hexadecimal digits. The characters 0x will be added to the left of the converted result for style 1. |
Implicit conversions
Implicit conversions do not require specification of either the CAST function or the CONVERT function. Explicit conversions require specification of the CAST function or the CONVERT function. The following illustration shows all explicit and implicit data type conversions allowed for SQL Server system-supplied data types. These include bigint, and sql_variant, and xml. There is no implicit conversion on assignment from the sql_variant data type, but there is implicit conversion to sql_variant.
Tip
The Microsoft Download Center has this chart available for download as a PDF file.
When you convert between datetimeoffset and the character types char, nchar, nvarchar, and varchar, the converted time zone offset part should always have double digits for both HH and MM. For example, -08:00.
Note
Because Unicode data always uses an even number of bytes, use caution when you convert binary or varbinary to or from Unicode supported data types. For example, the following conversion does not return a hexadecimal value of 41. It returns a hexadecimal value of 4100:
SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary)
.Large-value data types
Large-value data types have the same implicit and explicit conversion behavior as their smaller counterparts - specifically, the nvarchar, varbinary, and varchar data types. However, consider the following guidelines:
- Conversion from image to varbinary(max), and vice-versa, operates as an implicit conversion, as do conversions between text and varchar(max), and ntext and nvarchar(max).
- Conversion from large-value data types, such as varchar(max), to a smaller counterpart data type, such as varchar, is an implicit conversion, but truncation occurs if the size of the large value exceeds the specified length of the smaller data type.
- Conversion from nvarchar, varbinary, or varchar to their corresponding large-value data types happens implicitly.
- Conversion from the sql_variant data type to the large-value data types is an explicit conversion.
- Large-value data types cannot be converted to the sql_variant data type.
For more information about conversion from the xml data type, see Create Instances of XML Data.
xml data type
When you explicitly or implicitly cast the xml data type to a string or binary data type, the content of the xml data type is serialized based on a defined set of rules. For information about these rules, see Define the Serialization of XML Data. For information about conversion from other data types to the xml data type, see Create Instances of XML Data.
text and image data types
The text and image data types do not support automatic data type conversion. You can explicitly convert text data to character data, and image data to binary or varbinary, but the maximum length is 8000 bytes. If you try an incorrect conversion, for example trying to convert a character expression that includes letters to an int, SQL Server returns an error message.
Output Collation
When the CAST or CONVERT functions output a character string, and they receive a character string input, the output has the same collation and collation label as the input. If the input is not a character string, the output has the default collation of the database, and a collation label of coercible-default. For more information, see Collation Precedence (Transact-SQL).
To assign a different collation to the output, apply the COLLATE clause to the result expression of the CAST or CONVERT function. For example:
SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS
Truncating and rounding results
When converting character or binary expressions (binary, char, nchar, nvarchar, varbinary, or varchar) to an expression of a different data type, the conversion operation could truncate the output data, only partially display the output data, or return an error. These cases will occur if the result is too short to display. Conversions to binary, char, nchar, nvarchar, varbinary, or varchar are truncated, except for the conversions shown in the following table.
From data type | To data type | Result |
---|---|---|
int, smallint, or tinyint | char | * |
varchar | * | |
nchar | E | |
nvarchar | E | |
money, smallmoney, numeric, decimal, float, or real | char | E |
varchar | E | |
nchar | E | |
nvarchar | E |
* = Result length too short to display
E = Error returned because result length is too short to display.
E = Error returned because result length is too short to display.
SQL Server guarantees that only roundtrip conversions, in other words conversions that convert a data type from its original data type and back again, yield the same values from version to version. The following example shows such a roundtrip conversion:
Note
Do not construct binary values, and then convert them to a data type of the numeric data type category. SQL Server does not guarantee that the result of a decimal or numeric data type conversion, to binary, will be the same between versions of SQL Server.
The following example shows a resulting expression that is too small to display.
Here is the result set.
When you convert data types that differ in decimal places, SQL Server will sometimes return a truncated result value, and at other times it will return a rounded value. This table shows the behavior.
From | To | Behavior |
---|---|---|
numeric | numeric | Round |
numeric | int | Truncate |
numeric | money | Round |
money | int | Round |
money | numeric | Round |
float | int | Truncate |
float | numeric | Round Conversion of float values that use scientific notation to decimal or numeric is restricted to values of precision 17 digits only. Any value with precision higher than 17 rounds to zero. |
float | datetime | Round |
datetime | int | Round |
For example, the values 10.6496 and -10.6496 may be truncated or rounded during conversion to int or numeric types:
Results of the query are shown in the following table:
trunc1 | trunc2 | round1 | round2 |
---|---|---|---|
10 | -10 | 11 | -11 |
When converting data types where the target data type has fewer decimal places than the source data type, the value is rounded. For example, this conversion returns
$10.3497
:SELECT CAST(10.3496847 AS money);
SQL Server returns an error message when converting nonnumeric char, nchar, nvarchar, or varchar data to decimal, float, int, numeric. SQL Server also returns an error when an empty string (' ') is converted to numeric or decimal.
Certain datetime conversions are nondeterministic
The following table lists the styles for which the string-to-datetime conversion is nondeterministic.
All styles below 1001 | 106 |
107 | 109 |
113 | 130 |
1 With the exception of styles 20 and 21
For more information, see Nondeterministic conversion of literal date strings into DATE values.
Supplementary characters (surrogate pairs)
Starting with SQL Server 2012 (11.x), when using supplementary character (SC) collations, a CAST operation from nchar or nvarchar to an nchar or nvarchar type of smaller length will not truncate inside a surrogate pair. Instead, the operation truncates before the supplementary character. For example, the following code fragment leaves
@x
holding just 'ab'
. There is not enough space to hold the supplementary character.When using SC collations, the behavior of
CONVERT
, is analogous to that of CAST
.Compatibility support
In earlier versions of SQL Server, the default style for CAST and CONVERT operations on time and datetime2 data types is 121, except when either type is used in a computed column expression. For computed columns, the default style is 0. This behavior impacts computed columns when they are created, used in queries involving auto-parameterization, or used in constraint definitions.
Under compatibility level 110 and higher, the CAST and CONVERT operations on the time and datetime2 datatypes always have 121 as the default style. If a query relies on the old behavior, use a compatibility level less than 110, or explicitly specify the 0 style in the affected query.
Upgrading the database to compatibility level 110 and higher will not change user data that has been stored to disk. You must manually correct this data as appropriate. For example, if you used SELECT INTO to create a table from a source containing a computed column expression described above, the data (using style 0) would be stored rather than the computed column definition itself. You must manually update this data to match style 121.
Examples
A. Using both CAST and CONVERT
These examples retrieve the name of the product, for those products that have a
3
as the first digit of list price, and converts their ListPrice
values to int
.B. Using CAST with arithmetic operators
This example calculates a single column computation (
Computed
) by dividing the total year-to-date sales (SalesYTD
) by the commission percentage (CommissionPCT
). This value is rounded to the nearest whole number and is then CAST to an int
data type.Here is the result set.
C. Using CAST to concatenate
This example concatenates noncharacter expressions by using CAST. It uses the AdventureWorksDW database.
Here is the result set.
D. Using CAST to produce more readable text
This example uses CAST in the SELECT list, to convert the
Name
column to a char(10) column. It uses the AdventureWorksDW database.Here is the result set.
E. Using CAST with the LIKE clause
This example converts the
money
column SalesYTD
values to data type int
, and then to data typechar(20)
, so that the LIKE
clause can use it.Here is the result set.
F. Using CONVERT or CAST with typed XML
These examples show use of CONVERT to convert data to typed XML, by using the XML Data Type and Columns (SQL Server).
This example converts a string with white space, text and markup into typed XML, and removes all insignificant white space (boundary white space between nodes):
This example converts a similar string with white space, text and markup into typed XML and preserves insignificant white space (boundary white space between nodes):
This example casts a string with white space, text, and markup into typed XML:
See Create Instances of XML Data for more examples.
G. Using CAST and CONVERT with datetime data
Starting with GETDATE() values, this example displays the current date and time, uses
CAST
to change the current date and time to a character data type, and then uses CONVERT
to display the date and time in the ISO 8601
format.Here is the result set.
This example is approximately the opposite of the previous example. This example displays a date and time as character data, uses
CAST
to change the character data to the datetime
data type, and then uses CONVERT
to change the character data to the datetime
data type.Here is the result set.
H. Using CONVERT with binary and character data
These examples show the results of binary and character data conversion, using different styles.
Here is the result set.
This example shows that Style 1 can force result truncation. The characters 0x in the result set force the truncation.
Here is the result set.
This example shows that Style 2 does not truncate the result, because the result does not include the characters 0x.
Here is the result set.
Convert the character value 'Name' to a binary value.
Here is the result set.
Here is the result set.
Here is the result set.
I. Converting date and time data types
This example shows the conversion of date, time, and datetime data types.
Examples: Azure SQL Data Warehouse and Parallel Data Warehouse
J. Using CAST and CONVERT
This example retrieves the name of the product for those products that have a
3
in the first digit of their list price, and converts the ListPrice
of these products to int. It uses the AdventureWorksDW database.This example shows the same query, using CONVERT instead of CAST. It uses the AdventureWorksDW database.
K. Using CAST with arithmetic operators
This example calculates a single column value by dividing the product unit price (
UnitPrice
) by the discount percentage (UnitPriceDiscountPct
). This result is then rounded to the nearest whole number, and finally converted to an int
data type. This example uses the AdventureWorksDW database.Here is the result set.
L. Using CAST with the LIKE clause
This example converts the money column
ListPrice
to an int type, and then to a char(20) type, so that the LIKE clause can use it. This example uses the AdventureWorksDW database.M. Using CAST and CONVERT with datetime data
This example displays the current date and time, uses CAST to change the current date and time to a character data type, and finally uses CONVERT display the date and time in the ISO 8601 format. This example uses the AdventureWorksDW database.
Here is the result set.
This example is the rough opposite of the previous example. This example displays a date and time as character data, uses CAST to change the character data to the datetime data type, and then uses CONVERT to change the character data to the datetime data type. This example uses the AdventureWorksDW database.
Here is the result set.
See also
Data Type Conversion (Database Engine)
FORMAT (Transact-SQL)
STR (Transact-SQL)
SELECT (Transact-SQL)
System Functions (Transact-SQL)
Write International Transact-SQL Statements
FORMAT (Transact-SQL)
STR (Transact-SQL)
SELECT (Transact-SQL)
System Functions (Transact-SQL)
Write International Transact-SQL Statements
I want to convert a byte array to an image.
This is my database code from where I get the byte array:
My conversion code:
When I reach the line with a comment, the following exception occurs:
Parameter is not valid.
How can I fix whatever is causing this exception?
Tanzeel ur RahmanTanzeel ur Rahman
11 Answers
You are writing to your memory stream twice, also you are not disposing the stream after use.You are also asking the image decoder to apply embedded color correction.
Try this instead:
HolstebroeHolstebroe
Maybe I'm missing something, but for me this one-liner works fine with a byte array that contains an image of a JPEG file.
EDIT:
See here for an updated version of this answer: How to convert image in byte array
Community♦
RenniePetRenniePet
Community♦
Ali GhAli Gh
I'd like to note there is a bug in solution provided by @isaias-b.
That solution assume that
stride
is equal to row length. But it is not always true. Due to memory alignments performed by GDI, stride can be greater then row length. This must be taken into account. Otherwise invalid shifted image will be generated. Padding bytes in each row will be ignored.The stride is the width of a single row of pixels (a scan line), rounded up to a four-byte boundary.
Fixed code:
To illustrate what it can lead to, let's generate
PixelFormat.Format24bppRgb
gradient image 101x101:If we will copy entire array as-is to address pointed by
bmpData.Scan0
, we will get following image. Image shifting because part of image was written to padding bytes, that was ignored. Also that is why last row is incomplete:But if we will copy row-by-row shifting destination pointer by
bmpData.Stride
value, valid imaged will be generated:Stride also can be negative:
If the stride is positive, the bitmap is top-down. If the stride is negative, the bitmap is bottom-up.
But I didn't worked with such images and this is beyond my note.
Related answer: C# - RGB Buffer from Bitmap different from C++
lorondlorond
there is a simple approach as below, you can use
FromStream
method of an image to do the trick,Just remember to use System.Drawing
;AliAli
YahiaYahia
All presented answers assume that the byte array contains data in a known file format representation, like: gif, png or jpg. But i recently had a problem trying to convert
byte[]
s, containing linearized BGRA information, efficiently into Image
objects. The following code solves it using a Bitmap
object.This is a slightly variation of a solution which was posted on this site.
isaias-bisaias-b
You haven't declared returnImage as any kind of variable :)
This should help:
LeeCamblLeeCambl
This is inspired by Holstebroe's answer, plus comments here: Getting an Image object from a byte array
Community♦
RenniePetRenniePet
Most of the time when this happens it is bad data in the SQL column. This is the proper way to insert into an image column:
Most people do it incorrectly this way:
GPGVMGPGVM
Agus ApriantoroAgus Apriantoro