Cast Specification
Wed, 03/04/2009 - 20:02 | 
hdanzl
Specify a data conversion.
Syntax
|
| |
NULL |
Usage
The CAST function converts data of one type to a different type, subject to the conversion rules below.
Notes
| ♦ | Numeric values can be converted to any other numeric data type. If the target type has a smaller scale than the source value, then the fractional component is truncated. An exception is raised if the conversion would lead to a loss of any leading significant digits. |
| ♦ | Numeric values can be converted to any character string type. |
| ♦ | Character strings can be converted to other character string types. If the string being converted has more characters than the maximum length of the target type, then the string is truncated to fit the length of the target type. |
| ♦ | Character strings can be converted to any other data type, with the restriction that the contents of the character string must make sense for the target type. For example, if the target type is DATE, then the character string must mimic the pattern of a DATE literal – four digits, a hyphen, two digits, another hyphen, and two digits. |
| ♦ | A DATE value can be converted to a character string or a TIMESTAMP with the time part filled in with 00:00:00. |
| ♦ | A TIME value can be converted to a character string or a TIMESTAMP with the date part filled in with the current date. |
| ♦ | A TIMESTAMP value can be converted to a character string, a DATE or a TIME. |
| ♦ | Values of any datetime type can be converted to a numeric type. This functionality is a NexusDB extension. |
| ♦ | Boolean values can be converted to a character string. The boolean value TRUE is converted to 'True' and FALSE is converted to 'False'. |
| ♦ | The expression CAST ( NULL AS data-type ) is used to associate a data type with NULL. |
Examples
| 1) | The following example casts a character string value to DOUBLE PRECISION: |
CAST( '2.75' AS DOUBLE PRECISION )
| 2) | The following example casts an integer value to FLOAT: |
CAST( 50 AS FLOAT )
| 3) | The following example casts a character string value to DATE: |
CAST( '2004-10-15' AS DATE )
| 4) | The following example casts the current datetime value to VARCHAR: |
CAST( CURRENT_TIMESTAMP AS VARCHAR(19) )
| 5) | The following example casts the null value to DECIMAL: |
CAST ( NULL AS DECIMAL )
Conformance
|
SQL:2003 standard NexusDB extensions |
- - |
Core SQL Casting datetime values to a numeric type |
Tags:
Comments
Clarification
This statement that is in the document
>An exception is raised if the conversion would lead to a loss of any leading significant digits.
We took it to mean that if I tried to CAST an extended column containing 0.3839493 to an INTEGER it would raise an exception because of the lost of significant digits. BUT, when trying this it returns 0 as expected.
What does that statement mean then ?
Questions? Problems?
- Interested to buy but something put you off?
- Can't find what you're looking for?
- Have a suggestion?
Let us know. If you're having trouble contacting us, please check here.
Site Updates
- Dec 4 2024 - 1:53pm
- Dec 4 2024 - 1:48pm
- Nov 19 2024 - 1:04pm
- Oct 17 2024 - 10:27am
- Jul 24 2024 - 1:22pm
It means an exception will
It means an exception will be raised if the source can't fit in the target's range, eg casting a large integer value (999999999) to a byte would throw the exception.