Home » MySQL » MySQL date/time Functions

MySQL STR_TO_DATE() Function

MySQL | STR_TO_DATE() Function: Learn about the STR_TO_DATE() function, how it works, its usages, syntax, and examples.
Submitted by Apurva Mathur, on October 13, 2022

STR_TO_DATE() Function

As the name suggests, the STR_TO_DATE() function converts the string into the date format. We have to provide a string and format in which we want our date to be displayed and this function converts that string into the particular format which you have provided.

STR_TO_DATE() Syntax

SELECT STR_TO_DATE(string, format);

STR_TO_DATE() Parameter(s)

This function takes two parameters:

• string: The first parameter which you have to provide is a string. A string is a collection of characters. Here you have to provide a date value in the form of a string.
• format: In this function, some pre-defined formats are provided which helps in the conversion if the dates, these formats are as follows:
  • %a: This is an abbreviation that we can use if we want to know the weekday name i.e., Sunday to Saturday.
  • %b: This is an abbreviation that we can use if we want to know the month name i.e., January to December.
  • %c: This is an abbreviation that we can use if we want to know the month name numerically i.e., 1 to 12 (1=Jan,2=Feb. etc)
  • %D: This will give you the month number with the proper suffix.
  • %d: This will give you the month number without any suffix.
  • %e: This will give you the month number without any suffix.
  • %f: This will give you the detail about the microseconds (000000 to 999999).
  • %H: This will give the detail about hours in 12 hours format
  • %h: This will give you the result about hours in 24 hours format.
  • %I: This will give the detail about hours in 12 hours format.
  • %i: This will give the detail about minutes (00 to 59).
  • %j: This will tell you the day of the year (001 to 366)
  • %k: This will give you the result about hours in 24 hours format.
  • %M: This will give you the full month's name.
  • %m: This will give you the month name as a numeric value.
  • %p: This will tell you the whether it's a day or night i.e., AM OR PM.
  • %r: This will give you the time at 12-hour AM or PM format (hh:mm: ss AM/PM).
  • %S: This will give you the detail about the seconds (00 to 59).
  • %s: This will give you the detail about the seconds (00 to 59).
  • %T: This will give you the time in 24-hour format (hh:mm: ss)
  • %U: Week where Sunday is the first day of the week (00 to 53).
  • %u: It will tell you the Week where Monday is the first day of the week (00 to 53).
  • %V: It will tell you the Week where Sunday is the first day of the week (01 to 53). Used with %X.
  • %v: Week where Monday is the first day of the week (01 to 53). Used with %x.
  • %W: Weekday name in full (Sunday to Saturday)
  • %w: Day of the week where Sunday=0 and Saturday=6.
  • %X: Year for the week where Sunday is the first day of the week. Used with %V.
  • %x: Year for the week where Monday is the first day of the week. Used with %v.
  • %Y: It tells you the year as a numeric, 4-digit value
  • %y: It tells you the year as a numeric, 2-digit value.

STR_TO_DATE() Return Value

This function will return the date after converting it to the specified format.

MySQL STR_TO_DATE() Function Example 1

SELECT STR_TO_DATE('March 12 2019', '%M %d %Y');

MySQL STR_TO_DATE() Function Example 2

SELECT STR_TO_DATE("March 16 2022", "%M %e %Y");

MySQL STR_TO_DATE() Function Example 3

SELECT STR_TO_DATE("100212", "%h %i %f");

---