MariaDB Cast with Examples

In this MariaDB tutorial, we will study the use of MariaDB Cast and we will also cover some examples. There are lists of the topic that comes under discussion is given below:

• MariaDB Cast
• MariaDB Cast DateTime to Date
• MariaDB Cast as BigInt
• MariaDB Cast as Date
• MariaDB Cast as String to DateTime
• MariaDB Cast as Integer

MariaDB Cast

In MariaDB, the cast() function takes a value in one type and converts it into another type, just similar to the CONVERT() function.

The type can be of the following values:

• BINARY
• CHAR
• DATE
• DATETIME
• DECIMAL
• FLOAT
• DOUBLE
• INTEGER
• SIGNED [INTEGER]
• UNSIGNED [INTEGER]
• TIME
• VARCHAR

The main difference between the cast() and convert() function is that syntax in the cast() function is CAST(express as type) and CONVERT(… using …) syntax.

In Maria 10.0.4 or later, you can use cast() with the INTERVAL keyword.

The syntax of the Cast() function is given below:

CAST ( expr as type)

Note that when we cast to CHAR without specifying the character set, the collation_connection character set collation is used. When we use CHAR CHARACTER_SET, the default collation set will be used.

SELECT COLLATION( CAST( 123 AS CHAR));

The sample example of the cast() function is given below:

SELECT CAST(123 AS CHAR);

Read MariaDB Substring

MariaDB Cast DateTime to Date

In this section, we will understand how to convert DateTime datatype to date datatype using MariaDB cast. DateTime datatype in MariaDB is used to represent a combination of date and time values.

We will demonstrate the example of DateTime which is given below. The conversion of DateTime datatype by convert() function is given below:

SELECT Convert('2021-12-31 12:45:56',date);

If you try to use the now() function under the cast() function it will change output as a date. Now() function in MariaDB returns the current DateTime value and we have converted that DateTime value into date only using the CAST() function.

The sample example of now() function under the cast() function is given below:

SELECT CAST(NOW() AS DATE) AS Real_Date;

Read MariaDB LIMIT

MariaDB Cast as Bigint

In MariaDB, bigint is a larger integer number that is much bigger than an integer. The size range is -9223372036854775808 to 9223372036854775808. If the column value is set to ZEROFILL, then all the values will be prepended by zeros so that the BIGINT value contains a value of M digits.

[NOTE]: If you try to cast as bigint() function, then it will give an error. Let me show you a sample example to make it understandable which is given below:

SELECT CAST('2147483699' as BigInt);

As the error explains, you have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘bigint)’ at line 1.

Read MariaDB varchar

MariaDB Cast as Date

In MariaDB, the date function has supported a range of dates which is from ‘1000-01-01’ to ‘9999-12-31’. It displays DATE value ‘YYYY-MM-DD’ format but it can also be assigned in a looser format, including strings or numbers as long as it is easy to understand by the user. It includes the short year, a YY-MM-DD format, no delimiter YYYYMMDD.

The sample example of creating a table US_DATE by using date constraint is given below:

CREATE TABLE US_DATE (D DATE);

INSERT INTO US_DATE VALUES('2010-01-02'),('2011-01-25'),('120421'),('13*04*21');

SELECT * FROM US_DATE;

The method to cast() function as Date() function is given below:

SELECT CAST(D AS DATE) AS 'CAST' FROM US_Date;

Read MariaDB LIKE Operator

MariaDB Cast String to DateTime

In MariaDB, DATETIME columns accept CURRENT_TIMESTAMP as the default value. The creation of DateTime datatype in the table r1 is given below:

CREATE TABLE US_DateTime(D DATETIME UNIQUE);

INSERT INTO US_DateTime('2021-12-31'),('2022-01-01 19:34:31');

SELECT * FROM US_DateTime;

The sample example of DateTime datatype to cast() function is given below:

SELECT CAST(D AS DATETIME) AS 'cast' from US_DateTime;

The sample example of timestamp with one expression expr is given below:

SELECT TIMESTAMP('2021-01-01');

To convert into a timestamp, you need to use the DateTime data type to convert into cast() function is given below:

SELECT CAST('2021-01-01 18:30:00' AS DATETIME) AS Real_Time;

Read MariaDB query examples

MariaDB Cast as Integer

In MariaDB, the Integer data type is a normal-sized integer and the range are between 0 to 4294967295. INTEGER is a synonym of INT. If the ZEROFILL attribute has been specified, the column will be UNSIGNED.

[Note]: INT4 is a synonym of INT.

The syntax of an integer is given below:

INT[(M)] [SIGNED | UNSIGNED | ZEROFILL]
INTEGER[(M)] [SIGNED | UNSIGNED | ZEROFILL]

The sample example of integer constraint is given below:

CREATE TABLE USA_Positive (A INT,B INT UNSIGNED,C INT ZEROFILL);

INSERT INTO USA_Positive VALUES(-10,10,10);

INSERT INTO USA_Positive VALUES(2147483647,2147483648,2147483648);

SELECT * FROM USA_Positive;

The conversion of cast() function as an integer is given below:

SELECT CAST(A as INT) as big_value FROM INTS;

SELECT CAST(B as INT) as big_value_of_B from INTS;

SELECT CAST(C as int) as big_value_of_C from ints;

Related MariaDB tutorials:

• How to Drop Column from MariaDB Table
• How to Add Column in MariaDB
• MariaDB Vs SQL Server
• How to Grant User Access to a MariaDB Database
• How To Check MariaDB Version

In this tutorial, we have learned about the MariaDB Cast using some examples. Moreover, we have also covered the following topics in this tutorial as given below:

• MariaDB Cast
• MariaDB Cast DateTime to Date
• MariaDB Cast as BigInt
• MariaDB Cast as Date
• MariaDB Cast as String to DateTime
• MariaDB Cast as Integer