# Conversion Functions¶

Casting from UNKNOWN type to all other scalar types is supported, e.g., cast(NULL as int).

## Cast to Integral Types¶

Integral types include bigint, integer, smallint, and tinyint.

### From integral types¶

Casting one integral type to another is allowed. When the input value exceeds the range of result type, a value of the result type is created forcedly with the input value.

Valid examples:

```SELECT cast(1234567 as bigint); -- 1234567
SELECT cast(12 as tinyint); -- 12
SELECT cast(1234 as tinyint); -- -46
SELECT cast(1234567 as smallint); -- -10617
```

### From floating-point types¶

Casting from floating-point input to an integral type truncates the input value. It is allowed when the truncated result exceeds the range of result type.

Valid examples

```SELECT cast(12345.12 as bigint); -- 12345
SELECT cast(12345.67 as bigint); -- 12345
SELECT cast(127.1 as tinyint); -- 127
SELECT cast(127.8 as tinyint); -- 127
SELECT cast(1234567.89 as smallint); -- -10617
SELECT cast(cast('inf' as double) as bigint); -- 9223372036854775807
SELECT cast(cast('nan' as double) as integer); -- 0
SELECT cast(cast('nan' as double) as smallint); -- 0
SELECT cast(cast('nan' as double) as tinyint); -- 0
SELECT cast(cast('nan' as double) as bigint); -- 0
```

### From strings¶

Casting a string to an integral type is allowed if the string represents a number within the range of result type. Casting from strings that represent floating-point numbers truncates the decimal part of the input value. Casting from invalid input values throws.

Valid examples

```SELECT cast('12345' as bigint); -- 12345
SELECT cast('+1' as tinyint); -- 1
SELECT cast('-1' as tinyint); -- -1
SELECT cast('12345.67' as bigint); -- 12345
SELECT cast('1.2' as tinyint); -- 1
SELECT cast('-1.8' as tinyint); -- -1
SELECT cast('+1' as tinyint); -- 1
SELECT cast('1.' as tinyint); -- 1
SELECT cast('-1' as tinyint); -- -1
SELECT cast('-1.' as tinyint); -- -1
SELECT cast('0.' as tinyint); -- 0
SELECT cast('.' as tinyint); -- 0
SELECT cast('-.' as tinyint); -- 0
```

Invalid examples

```SELECT cast('1234567' as tinyint); -- Out of range
SELECT cast('1a' as tinyint); -- Invalid argument
SELECT cast('' as tinyint); -- Invalid argument
SELECT cast('1,234,567' as bigint); -- Invalid argument
SELECT cast('1'234'567' as bigint); -- Invalid argument
SELECT cast('nan' as bigint); -- Invalid argument
SELECT cast('infinity' as bigint); -- Invalid argument
```

### From decimal¶

The decimal part will be truncated for casting to an integer. It is allowed when the truncated result exceeds the range of result type.

Valid examples

```SELECT cast(cast(2.56 as DECIMAL(6, 2)) as bigint); -- 2
SELECT cast(cast(3.46 as DECIMAL(6, 2)) as bigint); -- 3
SELECT cast(cast(5500.0 as DECIMAL(5, 1)) as tinyint); -- 124
SELECT cast(cast(2147483648.90 as DECIMAL(12, 2)) as tinyint); -- 0
SELECT cast(cast(2147483648.90 as DECIMAL(12, 2)) as integer); -- -2147483648
SELECT cast(cast(2147483648.90 as DECIMAL(12, 2)) as bigint); -- 2147483648
```

## Cast to Boolean¶

### From VARCHAR¶

The strings t, f, y, n, 1, 0, yes, no, true, false and their upper case equivalents are allowed to be casted to boolean. Casting from other strings to boolean throws.

Valid examples

```SELECT cast('1' as boolean); -- true
SELECT cast('0' as boolean); -- false
SELECT cast('t' as boolean); -- true (case insensitive)
SELECT cast('true' as boolean); -- true (case insensitive)
SELECT cast('f' as boolean); -- false (case insensitive)
SELECT cast('false' as boolean); -- false (case insensitive)
SELECT cast('y' as boolean); -- true (case insensitive)
SELECT cast('yes' as boolean); -- true (case insensitive)
SELECT cast('n' as boolean); -- false (case insensitive)
SELECT cast('no' as boolean); -- false (case insensitive)
```

Invalid examples

```SELECT cast('1.7E308' as boolean); -- Invalid argument
SELECT cast('nan' as boolean); -- Invalid argument
SELECT cast('infinity' as boolean); -- Invalid argument
SELECT cast('12' as boolean); -- Invalid argument
SELECT cast('-1' as boolean); -- Invalid argument
SELECT cast('tr' as boolean); -- Invalid argument
SELECT cast('tru' as boolean); -- Invalid argument
```

## Cast to String¶

### From TIMESTAMP¶

Casting a timestamp to a string returns ISO 8601 format with space as separator between date and time, and the year part is padded with zeros to 4 characters. The conversion precision is microsecond, and trailing zeros are not appended. When the year exceeds 9999, a positive sign is added.

Valid examples

```SELECT cast(cast('1970-01-01 00:00:00' as timestamp) as string); -- '1970-01-01 00:00:00'
SELECT cast(cast('2000-01-01 12:21:56.129' as timestamp) as string); -- '2000-01-01 12:21:56.129'
SELECT cast(cast('2000-01-01 12:21:56.100000' as timestamp) as string); -- '2000-01-01 12:21:56.1'
SELECT cast(cast('2000-01-01 12:21:56.129900' as timestamp) as string); -- '2000-01-01 12:21:56.1299'
SELECT cast(cast('10000-02-01 16:00:00.000' as timestamp) as string); -- '+10000-02-01 16:00:00'
SELECT cast(cast('0384-01-01 08:00:00.000' as timestamp) as string); -- '0384-01-01 08:00:00'
SELECT cast(cast('-0010-02-01 10:00:00.000' as timestamp) as string); -- '-0010-02-01 10:00:00'
```

## Cast to Date¶

### From strings¶

All Spark supported patterns are allowed:

• `[+-](YYYY-MM-DD)`

• `[+-]yyyy*`

• `[+-]yyyy*-[m]m`

• `[+-]yyyy*-[m]m-[d]d`

• `[+-]yyyy*-[m]m-[d]d *`

• `[+-]yyyy*-[m]m-[d]dT*`

The asterisk `*` in `yyyy*` stands for any numbers. For the last two patterns, the trailing `*` can represent none or any sequence of characters, e.g:

• “1970-01-01 123”

• “1970-01-01 (BC)”

All leading and trailing UTF8 white-spaces will be trimmed before cast. Casting from invalid input values throws.

Valid examples

```SELECT cast('1970' as date); -- 1970-01-01
SELECT cast('1970-01' as date); -- 1970-01-01
SELECT cast('1970-01-01' as date); -- 1970-01-01
SELECT cast('1970-01-01T123' as date); -- 1970-01-01
SELECT cast('1970-01-01 ' as date); -- 1970-01-01
SELECT cast('1970-01-01 (BC)' as date); -- 1970-01-01
```

Invalid examples

```SELECT cast('2012-Oct-23' as date); -- Invalid argument
SELECT cast('2012/10/23' as date); -- Invalid argument
SELECT cast('2012.10.23' as date); -- Invalid argument
```

## Cast to Decimal¶

### From varchar¶

Casting varchar to a decimal of given precision and scale is allowed. The behavior is similar with Presto except Spark allows leading and trailing white-spaces in input varchars.

Valid example

```SELECT cast(' 1.23' as decimal(38, 0)); -- 1
SELECT cast('1.23 ' as decimal(38, 0)); -- 1
SELECT cast('  1.23  ' as decimal(38, 0)); -- 1
SELECT cast(' -3E+2' as decimal(12, 2)); -- -300.00
SELECT cast('-3E+2 ' as decimal(12, 2)); -- -300.00
SELECT cast('  -3E+2  ' as decimal(12, 2)); -- -300.00
```

## Cast to Varbinary¶

### From integral types¶

Casting integral value to varbinary type is allowed. Bytes of input value are converted into an array of bytes in little-endian order. Supported types are tinyint, smallint, integer and bigint.

Valid example

```SELECT cast(cast(18 as tinyint) as binary); -- [12]
SELECT cast(cast(180 as smallint) as binary); -- [00 B4]
SELECT cast(cast(180000 as integer) as binary); -- [00 02 BF 20]
SELECT cast(cast(180000 as bigint) as binary); -- [00 00 00 00 00 02 BF 20]
```