MariaDB Str_To_Date [10 Amazing Examples]

If you are working with date and time data in MariaDB, you may need to convert strings to date/time values in order to perform various operations on the data. MariaDB provides several functions and techniques that you can use to perform this conversion.

In this MariaDB tutorial, we will explore some of the most common ways to convert strings to date/time values in MariaDB. We will cover the STR_TO_DATE function, which is specifically designed for this purpose, as well as the CAST function, which can be used to cast a string as a DATETIME data type.

We will also discuss the DATE_FORMAT function, which can be used to extract date and time information from a string, and the CONVERT_TZ function, which can be used to convert a date/time value to a different time zone.

Throughout the MariaDB tutorial, we will provide examples of how to use these functions and techniques in SELECT queries, as well as some tips and considerations to keep in mind when working with date and time data in MariaDB.

• How to use MariaDB Str_To_Date function
• How to set the Timezone using MariaDB Str_To_Date
• MariaDB Str_To_Date Milliseconds
• How to deal with Null returned by MariaDB Str_To_Date
• MariaDB String to Date Time
• How to Cast to Date in MariaDB
• How to Cast Datetime To Date in MariaDB
• How to Cast String to Date in MariaDB
• How to convert string to date in MariaDB
• MariaDB Format String to Date

MariaDB Str_To_Date

The STR_TO_DATE function in MariaDB is a useful tool for converting a string representation of a date and time into a date/time value. This function can be used to extract date/time information from strings in a variety of formats, and to store that information in a date/time column or variable.

The syntax is given below.

STR_TO_DATE(string, format)

Where,

• string: The string parameter is the string representation of the date and time that you want to convert.
• format: The format parameter is a string that specifies the format of the string parameter.

The format parameter can include various specifiers that represent different parts of the date and time, such as the year, month, day, hour, minute, and second. For example, the specifier %Y represents the year (e.g. 2021), %m represents the month (e.g. 01), and %d represents the day (e.g. 01).

Here is a complete list of the format specifiers that can be used with the STR_TO_DATE function in MariaDB:

• %a: abbreviated weekday name (e.g. Mon)
• %b: abbreviated month name (e.g. Jan)
• %c: month, numeric (e.g. 1)
• %D: day of the month with English suffix (e.g. 1st)
• %d: day of the month, numeric (e.g. 01)
• %e: day of the month, numeric (e.g. 1)
• %f: microseconds (e.g. 000000)
• %H: hour (00-23)
• %h: hour (01-12)
• %I: hour (01-12)
• %i: minutes, numeric (e.g. 00)
• %j: day of year (e.g. 001)
• %k: hour (0-23)
• %l: hour (1-12)
• %M: month name (e.g. January)
• %m: month, numeric (e.g. 01)
• %p: AM or PM
• %r: time, 12-hour (e.g. 01:00:00 AM)
• %S: seconds (e.g. 00

Let’s take an example by following the below steps:

First, create a table in MariaDB to store information about time zones currently being used in the United States(USA), you can use the following SQL statement:

CREATE TABLE time_zones (
  time_zone_id INT AUTO_INCREMENT PRIMARY KEY,
  time_zone_name VARCHAR(255) NOT NULL,
  city_name VARCHAR(255),
  date_for_zone VARCHAR(255),
  offset_time VARCHAR(255) NOT NULL
);

INSERT INTO time_zones (time_zone_name, city_name, date_for_zone,offset_time) VALUES
    ('Eastern Time', 'New York', '01:02:23','-05:00'),
    ('Central Time', 'Chicago', '01:02:23','-06:00'),
    ('Mountain Time', 'Salt Lake City','01:02:23','-07:00'),
    ('Pacific Time', 'Los Angeles','01:02:23','-08:00');

SELECT * FROM time_zones;

The columns specific_time contain the time of the different zones in the form of the string, let’s use the below command to convert the string to date datatype.

SELECT time_zone_name, city_name, STR_TO_DATE(date_for_zone, '%d:%m:%Y')
FROM time_zones;

Read: MariaDB Unique Key

MariaDB Str_To_Date Timezone

One thing to keep in mind when using the STR_TO_DATE function is that the resulting date/time value will be stored in the database using the default time zone. This can be an issue if the string representation of the date and time includes a time zone offset, but the default time zone is different.

To handle this situation, you can use the CONVERT_TZ function to convert the date/time value to the desired time zone. The CONVERT_TZ function takes three arguments: the date/time value, the source time zone, and the destination time zone.

Here is an example of how to use the CONVERT_TZ function in combination with the STR_TO_DATE function.

SELECT CONVERT_TZ(STR_TO_DATE('2021-01-01', '%Y-%m-%d'), '-05:00', '+00:00') AS date_time;

The above query will convert the string ‘2021-01-01T00:00:00-05:00’ (which represents a date and time in the Eastern Time zone) to a date/time value in the UTC time zone. The STR_TO_DATE function extracts the date/time information from the string, and the CONVERT_TZ function converts it to the desired time zone.

The output of the example query would be a single row with a single column called date_time, containing the following value.

+---------------------+
| date_time           |
+---------------------+
| 2021-01-01 05:00:00 |
+---------------------+

The above output represents the date and time ‘2021-01-01 00:00:00’ in the UTC time zone, which is equivalent to the date and time ‘2021-01-01 00:00:00-05:00’ in the Eastern Time zone (the time zone specified in the original string).

This is how to change or convert the Time zone from one to another using the CONVERT_TZ function of MariaDB.

Read: MariaDB Difference Between Two Dates

MariaDB Str_To_Date Milliseconds

The STR_TO_DATE function in MariaDB does not have a built-in specifier for milliseconds. However, you can still parse a string representation of a date and time that includes milliseconds by using a custom format string.

For example, let’s say you have a string in the following format: ‘YYYY-MM-DD HH:MM:SS.ffffff’, where ‘ffffff’ represents the milliseconds as a six-digit integer. You can use the following format parameter to parse this string using the STR_TO_DATE function:

'%Y-%m-%d %H:%i:%s.%f'

Let’s take an example of how to use the STR_TO_DATE function with this format parameter.

SELECT STR_TO_DATE('2021-01-01 12:00:00.123456', '%Y-%m-%d %H:%i:%s.%f') AS date_time;

The above query will convert the string ‘2021-01-01 12:00:00.123456’ into a date/time value and return it as the date_time column. The format parameter specifies that the string parameter is in the format ‘YYYY-MM-DD HH:MM:SS.ffffff’, where ‘ffffff’ represents the milliseconds.

Read: MariaDB Date Greater Than

MariaDB Str_To_Date Null

The STR_TO_DATE function in MariaDB is a useful tool for converting a string representation of a date and time into a date/time value. However, there are situations where the STR_TO_DATE function may return NULL, indicating that it was unable to parse the input string.

There are several reasons why the STR_TO_DATE function may return NULL. Some common causes include:

• An incorrect format specifier in the format parameter: The format parameter must match the format of the string parameter, so if the specifiers in the format parameter do not match the corresponding characters in the string parameter, the STR_TO_DATE function will return NULL.
• An invalid value in the string parameter: The STR_TO_DATE function can only parse strings that are in a valid date and time format. If the string parameter contains an invalid date and time value, the STR_TO_DATE function will return NULL.
• An issue with the data type of the string parameter: The STR_TO_DATE function expects a string parameter, so if you pass in a value of a different data type (e.g. an integer), the STR_TO_DATE function will return NULL.

To handle cases where the STR_TO_DATE function returns NULL, you can use the IFNULL function to provide a default value. For example:

SELECT IFNULL(STR_TO_DATE(date_string, '%Y-%m-%d'), '0000-00-00') AS date_time;

The above query will attempt to convert the date_string column to a date/time value using the STR_TO_DATE function. If the STR_TO_DATE function returns NULL, the IFNULL function will return the default value ‘0000-00-00’ instead.

Read: MariaDB Check Empty String

MariaDB Cast to Date

In MariaDB, you can use the CAST function to convert a value from one data type to another. One common use case for the CAST function is to convert a string representation of a date and time into a date/time value.

To convert a string to a date/time value using the CAST function, you can use the following syntax.

CAST(string AS DATETIME)

Where,

• string: The string parameter is the string representation of the date and time that you want to convert.
• DATETIME: The DATETIME data type specifies that the result should be a date/time value.

Let’s take an example of how to use the CAST function to convert a string to a date/time value in a SELECT query.

SELECT CAST('2023-01-01' AS DATETIME) AS date_time;

The above query will convert the string ‘2023-01-01’ into a date/time value and return it as the date_time column. This is how to cast the string representation of the date-time into date/time value.

Read: MariaDB Check String Length

MariaDB Cast Datetime To Date

In MariaDB, we already know that we can use the CAST function to convert a value from one data type to another. One common use case for the CAST function is to convert a date/time value into a date value (i.e. a value that includes only the date part, without the time part).

To convert a date/time value to a date value using the CAST function, you can use the following syntax.

CAST(datetime_value AS DATE)

Where,

• datetime_value: The datetime_value parameter is the date/time value that you want to convert.
• DATE: The DATE data type specifies that the result should be a date value. The DATE data type specifies that the result should be a date value.

Let’s take an example of how to use the CAST function to convert a date/time value to a date value in a SELECT query.

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

The above query will convert the current date and time (returned by the NOW() function) into a date value and return it as the date column.

It is important to note that the CAST function will remove the time part of the date/time value when converting it to a date value.

Read: MariaDB Check If Rows Exists

MariaDB String to Date Conversion

In MariaDB, there are several ways to convert a string representation of a date and time into a date/time value. Here are some common techniques for performing this conversion.

• Using the STR_TO_DATE function: The STR_TO_DATE function is specifically designed for converting strings to date/time values. To use this function, you need to provide a string parameter that contains the date and time information, and a format parameter that specifies the format of the string parameter. The format parameter can include various specifiers that represent different parts of the date and time, such as the year, month, day, hour, minute, and second.

Let’s take examples to illustrate how to convert strings to date/time values in MariaDB using the STR_TO_DATE.

SELECT STR_TO_DATE('2022-01-01', '%Y-%m-%d') AS date_time;

The above example uses the STR_TO_DATE function to convert the string ‘2022-01-01’ into a date/time value. The format parameter specifies that the string parameter is in the format ‘YYYY-MM-DD’.

• Using the CAST function: The CAST function can be used to convert a string to a date/time value by casting the string as a DATETIME data type. This technique is similar to using the STR_TO_DATE function, but it does not require a format parameter. Instead, the CAST function will use the default date/time format for the current session to interpret the string.

Let’s take examples to illustrate how to convert strings to date/time values in MariaDB using the CAST.

SELECT CAST('2021-01-01' AS DATETIME) AS date_time;

The above example uses the CAST function to convert the string ‘2021-01-01’ into a date/time value. The DATETIME data type specifies that the result should be a date/time value.

• Using the DATE_FORMAT function: The DATE_FORMAT function can be used to extract date/time information from a string and return it as a date/time value. To use this function, you need to provide a string parameter that contains the date and time information, and a format parameter that specifies the format of the string parameter. The DATE_FORMAT function will return the date/time information as a string in the specified format, which you can then convert to a date/time value using the STR_TO_DATE or CAST function.

Let’s take examples to illustrate how to convert strings to date/time values in MariaDB using the DATE_FORMAT.

SELECT STR_TO_DATE(DATE_FORMAT('2023-01-04', '%Y-%m-%d'), '%Y-%m-%d') AS date_time;

The above example uses the DATE_FORMAT function to extract the date/time information from the string ‘2023-01-04’, and then uses the STR_TO_DATE function to convert the resulting string to a date/time value.

The DATE_FORMAT function returns the date/time information as a string in the specified format (‘YYYY-MM-DD’), which is then passed as the string parameter to the STR_TO_DATE function. The format parameter of the STR_TO_DATE function specifies that the string parameter is in the same format (‘YYYY-MM-DD’).

Read: MariaDB Insert If Not Exists

MariaDB Cast String to Date

In MariaDB, we already know how to use the CAST function to convert a string representation of a date and time into a date/time value.

• To use the CAST function for this purpose, you need to provide a string parameter that contains the date and time information and specify the DATETIME data type as the target data type.

The syntax for using the CAST function to convert a string to a date/time value in MariaDB is given below.

CAST(string AS DATETIME)

Where,

• string: The string parameter is the string representation of the date and time that you want to convert.
• DATETIME: The DATETIME data type specifies that the result should be a date/time value.

Let’s take an example of how to use the CAST function to convert a string to a date/time value in a SELECT query.

SELECT CAST('2022-04-11' AS DATETIME) AS date_time;

The above query will convert the string ‘2022-04-11’ into a date/time value and return it as the date_time column.

Read: MariaDB Between + Examples

MariaDB Format String to Date

In MariaDB, you can use the DATE_FORMAT function to format a date/time value as a string. This can be useful for displaying dates and times in a specific format, or for converting a date/time value to a string that can be used as input to another function.

• To use the DATE_FORMAT function, you need to provide a date/time value as the first argument and a format string as the second argument. The format string can include various specifiers that represent different parts of the date and time, such as the year, month, day, hour, minute, and second.

The syntax is given below.

DATE_FORMAT(date, format)

Where,

• date: The date parameter is the date/time value that you want to format.
• format: The format parameter is a string that specifies the desired format of the output string.

Let’s take an example of how to use the DATE_FORMAT function to format a date/time value as a string in a SELECT query.

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %T') AS date_time;

The above query will return the current date and time (returned by the NOW() function) as a string in the format ‘YYYY-MM-DD HH:MM:SS’, and will return it as the date_time column.

Conclusion

In conclusion, we have explored some of the most common ways to convert strings to date/time values in MariaDB.

We have looked at the STR_TO_DATE function, which is specifically designed for this purpose, as well as the CAST function, which can be used to cast a string as a DATETIME data type.

We have also discussed the DATE_FORMAT function, which can be used to extract date and time information from a string, and the CONVERT_TZ function, which can be used to convert a date/time value to a different time zone.

Here is the complete list of topics that we covered

• How to use MariaDB Str_To_Date function
• How to set the Timezone using MariaDB Str_To_Date
• MariaDB Str_To_Date Milliseconds
• How to deal with Null returned by MariaDB Str_To_Date
• MariaDB String to Date Time
• How to Cast to Date in MariaDB
• How to Cast Datetime To Date in MariaDB
• How to Cast String to Date in MariaDB
• How to convert string to date in MariaDB
• MariaDB Format String to Date

You may also like to read the following MariaDB tutorials.

• MariaDB Delete From Statement
• How To Create Views In MariaDB
• MariaDB ENUM – Helpful Guide
• MariaDB If Null + Examples
• MariaDB Set Auto Increment Value
• MariaDB Drop Table + Examples