How the STR_TO_DATE() function works in Mariadb?

The STR_TO_DATE() function in MariaDB is used to parse a string representation of a date and time and convert it into a DATE or DATETIME value. This function is particularly useful when you need to convert date and time values from a non-standard format into a format that can be stored in a DATE or DATETIME column.

Syntax

The syntax for the MariaDB STR_TO_DATE() function is as follows:

STR_TO_DATE(str, format)

• str: A string representing the date and time to be parsed. This is a required parameter.
• format: A string specifying the format of the str parameter. This is also a required parameter.

The function returns a DATE or DATETIME value, depending on the format string specified. If the conversion fails, it returns NULL.

Here is the table of specifiers that can be used in the format string of the STR_TO_DATE(str, format) function in MariaDB:

These specifiers allow you to parse and format various components of dates and times, such as years, months, days, hours, minutes, seconds, and more. By combining these specifiers in the format string, you can handle a wide range of date and time formats when using the STR_TO_DATE() function in MariaDB.

Examples

Example 1: Converting a string to a DATE value

This example demonstrates how to convert a string representation of a date into a DATE value.

DROP TABLE IF EXISTS example;
CREATE TABLE example (date_col DATE);
INSERT INTO example (date_col) VALUES (STR_TO_DATE('2023-03-09', '%Y-%m-%d'));

SELECT * FROM example;

The following is the output:

+------------+
| date_col   |
+------------+
| 2023-03-09 |
+------------+

In this example, the string '2023-03-09' is converted to a DATE value using the format specifier '%Y-%m-%d', which represents the year, month, and day in the specified order.

Example 2: Converting a string to a DATETIME value

This example shows how to convert a string representation of a date and time into a DATETIME value.

DROP TABLE IF EXISTS example;
CREATE TABLE example (datetime_col DATETIME);
INSERT INTO example (datetime_col) VALUES (STR_TO_DATE('2023-03-09 14:30:00', '%Y-%m-%d %H:%i:%s'));

SELECT * FROM example;

The following is the output:

+---------------------+
| datetime_col        |
+---------------------+
| 2023-03-09 14:30:00 |
+---------------------+

In this example, the string '2023-03-09 14:30:00' is converted to a DATETIME value using the format specifier '%Y-%m-%d %H:%i:%s', which includes the year, month, day, hour, minute, and second.

Example 3: Handling different date formats

The STR_TO_DATE() function can handle various date and time formats by specifying the appropriate format string.

DROP TABLE IF EXISTS example;
CREATE TABLE example (date_col DATE);
INSERT INTO example (date_col) VALUES
    (STR_TO_DATE('09/03/2023', '%d/%m/%Y')),
    (STR_TO_DATE('March 9, 2023', '%M %d, %Y')),
    (STR_TO_DATE('2023.03.09', '%Y.%m.%d'));

SELECT * FROM example;

The following is the output:

+------------+
| date_col   |
+------------+
| 2023-03-09 |
| 2023-03-09 |
| 2023-03-09 |
+------------+

In this example, three different string representations of the same date are converted to DATE values using different format specifiers: '%d/%m/%Y', '%M %d, %Y', and '%Y.%m.%d'.

Example 4: Handling invalid input

If the input string does not match the specified format, the STR_TO_DATE() function returns NULL.

DROP TABLE IF EXISTS example;
CREATE TABLE example (date_col DATE);
INSERT INTO example (date_col) VALUES
    (STR_TO_DATE('2023/03/09', '%Y-%m-%d')),
    (STR_TO_DATE('09-03-2023', '%Y-%m-%d'));

The following is the output:

ERROR 1411 (HY000): Incorrect datetime value: '2023/03/09' for function str_to_date

In this example, MariaDB returns an error because the input string '2023/03/09' does not match the format '%Y-%m-%d', althought the second row is parsed correctly because the input string '09-03-2023' matches the specified format.

Example 5: Using format specifiers

The format string in the STR_TO_DATE() function can include various format specifiers to handle different date and time components. Here are a few examples:

DROP TABLE IF EXISTS example;
CREATE TABLE example (datetime_col DATETIME);
INSERT INTO example (datetime_col) VALUES
    (STR_TO_DATE('2023-03-09 14:30', '%Y-%m-%d %H:%i')),
    (STR_TO_DATE('2023/03/09 2:30:00 PM', '%Y/%m/%d %r')),
    (STR_TO_DATE('09 Mar 2023', '%d %b %Y'));

SELECT * FROM example;

The following is the output:

+---------------------+
| datetime_col        |
+---------------------+
| 2023-03-09 14:30:00 |
| 2023-03-09 14:30:00 |
| 2023-03-09 00:00:00 |
+---------------------+

In this example, different format specifiers are used to handle various date and time components, such as hours and minutes (%H:%i), AM/PM notation (%r), and abbreviated month names (%b).

Related Functions

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

• MariaDB DATE_FORMAT() function is used to format a DATE or DATETIME value as a string according to a specified format.
• MariaDB UNIX_TIMESTAMP() function is used to convert a DATE or DATETIME value to a Unix timestamp (seconds since the Unix epoch).
• MariaDB FROM_UNIXTIME() function is used to convert a Unix timestamp to a DATE or DATETIME value.
• MariaDB CONVERT_TZ() function is used to convert a DATETIME value from one time zone to another.

Conclusion

The STR_TO_DATE() function in MariaDB is a powerful tool for converting string representations of dates and times into DATE or DATETIME values. By providing the appropriate format string, it can handle a wide range of date and time formats, making it easier to work with data from various sources. Understanding the syntax and usage of this function is crucial for effective date and time handling in MariaDB.