How the TO_CHAR() function works in Mariadb?

The TO_CHAR() function in MariaDB is used to convert a date, datetime, time or timestamp value into a string representation.

Posted on

The TO_CHAR() function in MariaDB is used to convert a date, datetime, time or timestamp value into a string representation. It provides a flexible way to format and display date, time, and numeric values according to a specified pattern.

Syntax

The syntax of the MariaDB TO_CHAR() function is as follows:

TO_CHAR(expr, format)
  • expr: This is the expression to be converted. It can be a date, datetime, time or timestamp value.
  • format: This is a string specifying the format pattern to be used for the conversion.

The function returns a string representing the formatted value of the input expression based on the specified format pattern.

Examples

Example 1: Formatting a date

This example demonstrates how to use the TO_CHAR() function to format a date.

SELECT TO_CHAR(CURRENT_DATE(), 'YYYY-MM-DD') AS formatted_date;

The following is the output:

+----------------+
| formatted_date |
+----------------+
| 2024-03-10     |
+----------------+

In this example, the TO_CHAR() function converts the current date, obtained using CURRENT_DATE(), into a string formatted as ‘YYYY-MM-DD’.

Example 2: Formatting a time

This example shows how to format a time value using the TO_CHAR() function.

SELECT TO_CHAR(CURRENT_TIME(), 'HH24:MI:SS') AS formatted_time;

The following is the output:

+----------------+
| formatted_time |
+----------------+
| 14:59:12       |
+----------------+

In this example, the TO_CHAR() function formats the current time, obtained using CURRENT_TIME(), into a string with the format ‘HH24:MI:SS’, which represents the 24-hour clock format.

Example 3: Formatting a datetime value

This example shows how to format a datetime value using the TO_CHAR() function.

SELECT TO_CHAR(CURRENT_TIMESTAMP(), 'YYYY-MM-DD HH24:MI:SS') AS formatted_datetime;

The following is the output:

+---------------------+
| formatted_datetime  |
+---------------------+
| 2024-03-10 14:59:53 |
+---------------------+

In this example, the TO_CHAR() function formats the current timestamp, obtained using CURRENT_TIMESTAMP(), into a string with the pattern ‘YYYY-MM-DD HH24:MI:SS’, which includes both the date and time components.

The following are some functions related to the MariaDB TO_CHAR() function:

  • MariaDB STR_TO_DATE() function is used to convert a string into a date value.
  • MariaDB DATE_FORMAT() function is another way to format date and time values into strings.

Conclusion

The TO_CHAR() function in MariaDB is a versatile tool for formatting and converting datetime and numeric values into string representations. It provides a wide range of formatting options through the use of format patterns, allowing developers to display data in a readable and customized manner. By understanding the usage and capabilities of this function, along with related functions, developers can effectively present and manipulate date, time, and numeric data in their MariaDB applications.