STR_TO_DATE parsing in MySQL

Introduction

STR_TO_DATE helps convert text into date or time values so you can work with them easily in your database.

You have dates stored as text and want to sort or filter by date.

You receive date information in different formats and need to standardize it.

You want to compare or calculate with dates that are currently strings.

You import data from files where dates are not in date format.

You want to extract parts of a date from a text string.

Syntax

MySQL

STR_TO_DATE(string, format)

The string is the text you want to convert to a date or time.

The format tells MySQL how the date or time is written in the string.

Examples

Converts '2024-06-15' text to a date using year-month-day format.

MySQL

STR_TO_DATE('2024-06-15', '%Y-%m-%d')

Converts '15/06/2024' text to a date using day/month/year format.

MySQL

STR_TO_DATE('15/06/2024', '%d/%m/%Y')

Converts '06:30:00' text to a time using hours:minutes:seconds format.

MySQL

STR_TO_DATE('06:30:00', '%H:%i:%s')

Converts 'June 15, 2024' text to a date using full month name, day, and year.

MySQL

STR_TO_DATE('June 15, 2024', '%M %d, %Y')

Sample Program

This query converts the text '31-12-2023 23:59:59' into a datetime value using day-month-year and time format.

MySQL

SELECT STR_TO_DATE('31-12-2023 23:59:59', '%d-%m-%Y %H:%i:%s') AS parsed_datetime;

Important Notes

Make sure the format matches exactly how the date/time is written in the string.

If the string does not match the format, STR_TO_DATE returns NULL.

Common format codes: %Y = 4-digit year, %m = month number, %d = day, %H = hour (24h), %i = minutes, %s = seconds.

Summary

STR_TO_DATE converts text to date/time using a format string.

Use it to work with dates stored as text in your database.

Always match the format to your text to avoid errors.