MySQL Convert() function – A Quick Guide!

Filed Under: SQL
MySQL Convert() Function

Hello, readers! Yes, we are back with a new topic in MySQL. Today we will be having a look at MySQL Convert() function in detail.

SO, let us begin!!


First, what is MySQL Convert() function?

MySQL Convert() enables us to convert or change the data type of a particular variable to another data type.

By this, we can make changes to the data type of the variables/columns in the SQL table at runtime. Besides, it even manipulates the value of a variable from one character set to another character set.

Let us understand its necessity across real life scenarios.

Consider a situation wherein we fetch data from REST APIs and use a Python script to push the data in real-time into the MySQL database. In such a scenario, as the data is being fetched in real-time, we expect the column types to be raw.

Now, this is when MySQL Convert() function comes into the picture.

The MySQL CONVERT() function will help us convert the data type of the required columns into the desired type at an ease.


Syntax – Convert() function

Have a look at the below code!

Syntax:

CONVERT( input, data-type )
  • input: The data column whose data type needs to be converted.
  • data-type: The desired data type which the column should possess.

Examples – MySQL Convert() function

In this example, we have converted a string value into a DATE date type value.

1. Convert a String to DATE datatype

SELECT CONVERT("2020-04-04", DATE);

As seen below, the string value gets converted into a date time value following the format of date time.

Output:

CONVERT("2020-04-04", DATE)
2020-04-04T00:00:00.000Z

2. Convert a Number to CHAR datatype

Let us now convert a numeric value into CHAR data type value.

Have a look at the below code!

SELECT CONVERT(1556, char);

Output:

CONVERT(1556, char)
1556

3. Convert CHAR to INT in MySQL

Now, we have reversed the above case! We have converted the character value ‘1556’ into integer type value.

SELECT CONVERT('1556', INT);

Thus, post conversion this value 1556 would be considered as a numeric value.

Output:

CONVERT('1556', INT)
1556

4. Convert a string to TIME data type

Having done a few conversions, let us now convert the string type to a TIME data type value.

SELECT CONVERT("12:08:12", TIME);

By this, we set the data type to TIME which converts the value to this data type.

Output:

CONVERT("12:08:12", TIME)
12:08:12

Conclusion

By this, we have come to the end of this topic. Feel free to comment below, in case you come across any question.

For more such posts related to SQL, stay tuned!!

For a deeper understanding, do try to perform manipulation on the values after converting them into desired data types and let us know about it in the comment section!

Till then, Happy Learning!!:)

References – https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages