MySQL STR_TO_DATE() Function

The STR_TO_DATE() function in MySQL converts a string into a date or datetime value using a specified format. This is particularly useful when you need to parse date/time values stored as strings and work with them in MySQL's date or datetime functions.

Syntax

STR_TO_DATE(string, format);

Parameters:

string: The input string to be converted into a date/datetime.
format: The format string specifying how the date/time is represented in the input string.

Supported Format Specifiers

Specifier	Description
`%d`	Day of the month (01-31)
`%m`	Month number (01-12)
`%Y`	Year, 4 digits (e.g., 2025)
`%y`	Year, 2 digits (e.g., 25 for 2025)
`%H`	Hour (00-23)
`%h`	Hour (01-12, AM/PM format)
`%i`	Minutes (00-59)
`%s`	Seconds (00-59)
`%p`	AM or PM
`%b`	Abbreviated month name (e.g., Jan)
`%M`	Full month name (e.g., January)

For a full list of format specifiers, refer to the MySQL documentation.

Examples

1. Converting String to Date

SELECT STR_TO_DATE('15-01-2025', '%d-%m-%Y') AS parsed_date;

Output: 2025-01-15

2. Converting String to Datetime

SELECT STR_TO_DATE('15-01-2025 14:30:00', '%d-%m-%Y %H:%i:%s') AS parsed_datetime;

Output: 2025-01-15 14:30:00

3. Using Month Name

SELECT STR_TO_DATE('January 15, 2025', '%M %d, %Y') AS parsed_date;

Output: 2025-01-15

4. Converting Time

SELECT STR_TO_DATE('02:30 PM', '%h:%i %p') AS parsed_time;

Output: 14:30:00

Use Case: Inserting Data into a Table

If you have a table with a DATE column and your input data is in string format:

CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_date DATE
);

INSERT INTO events (event_date)
VALUES (STR_TO_DATE('15/01/2025', '%d/%m/%Y'));

Common Errors

Incorrect Format: If the format in the function does not match the input string, the result will be NULL.
```
SELECT STR_TO_DATE('2025-01-15', '%d-%m-%Y'); -- Output: NULL
```
Invalid Date Values: If the input string contains invalid date/time components, the result will be NULL.

Conclusion

The STR_TO_DATE() function is a powerful tool in MySQL for converting date and time values from string formats into proper date or datetime objects. Ensure that the format specifiers match the input string format for accurate conversions.

Souy Soeng

MySQL STR_TO_DATE() Function