|
convert()
The
Datatype Conversion Function
convert()
converts a given value into another
datatype
or returns a different display format of a
datetime datatype.
Syntax of the datatype conversion function convert()
convert (datatype [(length) | (precision[, scale])]
[null | not null], expression [, style])
Parameter of the datatype conversion function convert()
datatype
The
parameter
"datatype" is a datatype provided by the system. So for example,
char(10), unichar (10), varbinary (50) or int. The function can convert the parameter
"expression"
into the given datatype. The parameter "datatype" does not allow
user defined datatypes.
If Java is available in the database, there is additional possibility to declare Java-SQL classes from the current database as parameter for "datatype".
length
The parameter "length" is an optional parameter. It can be used with the datatypes
char,
nchar,
unichar,
univarchar,
varchar,
nvarchar,
binary
and
varbinary
. If no length is given,
ASE®
will cut-off the ouput for
character datatypes
after the 30th character and for
binary datatypes
after the 30th
byte
. The maximum value of the parameter "length" is
64K for character datatypes and for binary datatypes.
precision
The parameter "precision" gives the number of digits for the datatypes
numeric
or
decimal.
For the datatype
float
the parameter "precision" contains the number of binary digits in its mantissa, i.e. the number of logarithms after the comma.
If no value is given for the parameter "precision", the ASE default precision of 18 is used for numeric and decimal datatypes.
scale
The parameter "scale" declares the number of digits on the right side of the comma of a decimal value. The parameter "scale" is optional. If its is not set the default value of "0" is used.
null | not null
The parameter "null | not null" declares the so-called
nullability
of the return value. The parameter "null | not null" is optional. If it is not set, the return value will be converted into the same "nullability" like the "expression".
expression
The parameter "expression" gives the value which is to be converted from one datatype into the other. If Java is available in the database it is also additionally possible to convert values of the parameter "expression" into Java-SQL classes. If the target datatype, i.e. the datatype in which the parameter "expression" is to be converted is unichar, the default lenght of 30 Unicode-values is used if the lengt is not explicitly given.
style
The parameter "style" describes the output format of the datatype to be converted.
In order to convert the output format of the datatypes money or smallmoney to put a comma after every third digit, the
style-number
1 must be defined.
select convert(money, 10003452.234, 1)
go
-----
10,003,452.23
In order to convert the datatypes datetime or smalldatetime into a character datatype and to modify the output,
style-numbers are used.
In order to convert values of the datatype date into the datatype character, the
style-numbers
1 to 7 and 101 to 107 respectively and 10 to 12 and 120 to 112 respectively can be used. The default style number is 0 or 100 and defines the output in the format: mon dd yyyy hh:miAM (or PM ).
select convert(char(19), getdate())
go
-----
If the datatype date is converted into a "style" containing a time component this will by default be filled with "0" values (00:00:00:000).
During conversion of the datatype time into a character datatype the
style-numbers
8 or 9 and 108 or 109 respectively can be used to format the return value.
The default value is 100 (mon dd yyyy hh:miAM (oder PM)), i.e. the format of the default output if no parameter "style" is given. When converting teh datatype time into a
"style" with date values, the return value will contain the default date of
"Jan 1, 1900".
Example for the datatype conversion function convert()
Converts the current date into "style 3" which produces the output format DD/MM/YY
select convert(char(12), getdate(), 3)
go
-----
Usage of the ASE function convert()
The function convert() offers many possibilities to convert different datatypes into
other datatypes and to format the output of return values of date, time and money datatypes.
Therefore only a few special features and examples are mentioned.
When converting the datatype
unichar
into other datatypes or into another output format the return value can, at the most,
have the maximum lenght of the datatype to be converted. If the parameter length
(length of the datatype) is not given, a default length of 30
bytes is assumed.
select convert(varchar(3), unichar) from your_table
go
-----
AB
The parameter "null | not null" is usually used with
select into
to create a new
table
. Thus in the newly created table
nullable
columns can be defined with new datatypes.
select column, convert(char(255) not null, new_column)
into #temp_table
from your_table
See also:
ASE T-SQL - Aggregate Functions,
biginttohex(),
bintostr(),
cast(),
Datatype Conversion Functions,
explicit conversion,
hextobigint(),
hextoint(),
implicit conversions,
inttohex().
|
