# CAST or CONVERT

> **📝 Note**: Both the `CAST` and `CONVERT` commands perform the same operation.

Casts the input to the given data type.

There is usually no visible effect on the printed value; there is a change only in the rules for comparison and sorting.

## Syntax

```sql
CONVERT (input, {BINARY(length) | CHAR(length) | DATE | DATETIME[(prec)] |
  DECIMAL[(prec [, scale])] | TIME[(prec)] | SIGNED [INTEGER] | UNSIGNED [INTEGER]})

CAST (input AS {BINARY(length) | CHAR(length) | DATE | DATETIME[(prec)] |
  DECIMAL[(prec [, scale])] | TIME[(prec)] | SIGNED [INTEGER] | UNSIGNED [INTEGER]})

```

Note: Specifying the `CHAR` data type results in a `VARCHAR`. There is no separate `VARCHAR` data type option in this syntax.

## Return Type

The return type is the data type specified in the command.

## Examples

```sql
SELECT CONVERT(-123, UNSIGNED);

```

```output

+-------------------------+
| CONVERT(-123, UNSIGNED) |
+-------------------------+
|    18446744073709551493 |
+-------------------------+
```

```sql
SELECT '2019-01-01', CAST('2019-01-01 16:15:00' AS DATE);

```

```output

+------------+-------------------------------------+
| 2019-01-01 | CAST('2019-01-01 16:15:00' AS DATE) |
+------------+-------------------------------------+
| 2019-01-01 | 2019-01-01                          |
+------------+-------------------------------------+

```

```
SELECT CAST(123 AS CHAR(2));

```

```output

+-------------------------+
| CAST(123 AS CHAR(2))    |
+-------------------------+
|    12                   |
+-------------------------+

```

## Cast Operators `:>` and `!:>`

Two operators are provided for data type casting - `:>` for casting with the default error behavior, and `!:>` which does a "forceful" cast that always succeeds and produces the standard default value for the target data type even if the cast would otherwise produce an error. The data conversion behavior of` !:>` is the same as for [INSERT IGNORE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/insert.md).

> **📝 Note**: For a command that uses a casting operator, you can specify any data type after the casting operator `:>` or` !:>` to convert the input value, same as you would specify a column type in a `CREATE TABLE` statement.

## Examples

```sql
SELECT (1 :> DOUBLE);

```

```output

+---------------+
| (1 :> DOUBLE) |
+---------------+
|             1 |
+---------------+
1 row in set (0.015 sec)
```

```sql
SELECT (1 :> DECIMAL(18,2));

```

```output

+----------------------+
| (1 :> DECIMAL(18,2)) |
+----------------------+
|                 1.00 |
+----------------------+
```

```sql
SELECT '2019-01-01' !:> DATETIME;

```

```output

+---------------------------+
| '2019-01-01' !:> DATETIME |
+---------------------------+
| 2019-01-01 00:00:00       |
+---------------------------+
```

In the following example, the `SELECT `command returns an error using `:>`, while it succeeds and produces a zero value using `!:>`:

```sql
SELECT (NULL :> DECIMAL(18,2) NOT NULL);

```

```output

ERROR 2222 (HY000): Tried to convert NULL to a NOT NULL type
```

In the following example, `:>` operator is used with a string data type for case-sensitive search:

```sql
SELECT * FROM Tabview WHERE element_name = "var" :> VARCHAR (50) COLLATE utf8_bin;

```

```output

+------+--------------+
| id   | element_name |
+------+--------------+
|   20 | var          |
+------+--------------+
```

***

Modified at: May 8, 2025

Source: [/db/v9.1/reference/sql-reference/conditional-functions/cast-or-convert/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/conditional-functions/cast-or-convert/)

(An index of the documentation is available at /llms.txt)