CAST converts a value from one data type to another and provides
a data type to a dynamic parameter (?) or a NULL value.
CAST expressions
are permitted anywhere expressions are permitted.
Syntax CAST ( [ Expression | NULL | ? ]
AS Datatype) The data type to which you are
casting an expression is the target type. The data type of the expression
from which you are casting is the source type.
CAST conversions among SQL-92 data types The following
table shows valid explicit conversions between source types and target types
for SQL data types.
Explicit conversions between source types
and target types for SQL data typesThis table shows which explicit
conversions between data types are valid. The first column on the table lists
the source types, while the first row lists the target types. A "Y" indicates
that the source to the target is a valid conversion.
Types
S
M
A
L
L
I
N
T
I
N
T
E
G
E
R
B
I
G
I
N
T
D
E
C
I
M
A
L
R
E
A
L
D
O
U
B
L
E
F
L
O
A
T
C
H
A
R
V
A
R
C
H
A
R
L
O
N
G
V
A
R
C
H
A
R
C
H
A
R
F
O
R
B
I
T
D
A
T
A
V
A
R
C
H
A
R
F
O
R
B
I
T
D
A
T
A
L
O
N
G
V
A
R
C
H
A
R
F
O
R
B
I
T
D
A
T
A
C
L
O
B
B
L
O
B
D
A
T
E
T
I
M
E
T
I
M
E
S
T
A
M
P
SMALLINT
Y
Y
Y
Y
Y
Y
Y
Y
-
-
-
-
-
-
-
-
-
-
INTEGER
Y
Y
Y
Y
Y
Y
Y
Y
-
-
-
-
-
-
-
-
-
-
BIGINT
Y
Y
Y
Y
Y
Y
Y
Y
-
-
-
-
-
-
-
-
-
-
DECIMAL
Y
Y
Y
Y
Y
Y
Y
Y
-
-
-
-
-
-
-
-
-
-
REAL
Y
Y
Y
Y
Y
Y
Y
-
-
-
-
-
-
-
-
-
-
-
DOUBLE
Y
Y
Y
Y
Y
Y
Y
-
-
-
-
-
-
-
-
-
-
-
FLOAT
Y
Y
Y
Y
Y
Y
Y
-
-
-
-
-
-
-
-
-
-
-
CHAR
Y
Y
Y
Y
-
-
-
Y
Y
Y
-
-
-
Y
-
Y
Y
Y
VARCHAR
Y
Y
Y
Y
-
-
-
Y
Y
Y
-
-
-
Y
-
Y
Y
Y
LONG VARCHAR
-
-
-
-
-
-
-
Y
Y
Y
-
-
-
Y
-
-
-
-
CHAR FOR BIT DATA
-
-
-
-
-
-
-
-
-
-
Y
Y
Y
Y
Y
-
-
-
VARCHAR FOR BIT DATA
-
-
-
-
-
-
-
-
-
-
Y
Y
Y
Y
Y
-
-
-
LONG VARCHAR FOR BIT DATA
-
-
-
-
-
-
-
-
-
-
Y
Y
Y
Y
Y
-
-
-
CLOB
-
-
-
-
-
-
-
Y
Y
Y
-
-
-
Y
-
-
-
-
BLOB
-
-
-
-
-
-
-
-
-
-
-
-
-
-
Y
-
-
-
DATE
-
-
-
-
-
-
-
Y
Y
-
-
-
-
-
-
Y
-
Y
TIME
-
-
-
-
-
-
-
Y
Y
-
-
-
-
-
-
-
Y
Y
TIME STAMP
-
-
-
-
-
-
-
Y
Y
-
-
-
-
-
-
Y
Y
Y
If a conversion is valid, CASTs are allowed. Size incompatibilities
between the source and target types might cause runtime errors.
Notes In this discussion, the SQL-92
data types are categorized as follows:
- numeric
- exact numeric (SMALLINT, INTEGER, BIGINT, DECIMAL, NUMERIC)
- approximate numeric (FLOAT, REAL, DOUBLE PRECISION)
- string
- character string (CLOB, CHAR, VARCHAR, LONG VARCHAR)
- bit string (BLOB, CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, LONG VARCHAR FOR BIT DATA)
- date/time
Conversions from numeric types A numeric type can
be converted to any other numeric type. If the target type cannot represent
the non-fractional component without truncation, an exception is raised. If
the target numeric cannot represent the fractional component (scale) of the
source numeric, then the source is silently truncated to fit into the target.
For example, casting 763.1234 as INTEGER yields 763.
Conversions from and to bit strings Bit strings
can be converted to other bit strings, but not character strings. Strings
that are converted to bit strings are padded with trailing zeros to fit the
size of the target bit string. The BLOB type is more limited and requires
explicit casting. In most cases the BLOB type cannot be casted to and from
other types.
Conversions of date/time values A
date/time value can always be converted to and from a TIMESTAMP. If a DATE
is converted to a TIMESTAMP, the TIME component of the resulting TIMESTAMP
is always 00:00:00. If a TIME data value is converted to a TIMESTAMP, the
DATE component is set to the value of CURRENT_DATE at the time the CAST is
executed. If a TIMESTAMP is converted to a DATE, the TIME component is silently
truncated. If a TIMESTAMP is converted to a TIME, the DATE component is silently
truncated.
SELECT CAST (miles AS INT)
FROM Flights
-- convert timestamps to text
INSERT INTO mytable (text_column)
VALUES (CAST (CURRENT_TIMESTAMP AS VARCHAR(100)))
-- you must cast NULL as a data type to use it
SELECT airline
FROM Airlines
UNION ALL
VALUES (CAST (NULL AS CHAR(2)))
-- cast a double as a decimal
SELECT CAST (FLYING_TIME AS DECIMAL(5,2))
FROM FLIGHTS
-- cast a SMALLINT to a BIGINT
VALUES CAST (CAST (12 as SMALLINT) as BIGINT)