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');
Example 1: MySQL STR_TO_DATE() Function

MySQL STR_TO_DATE() Function Example 2

SELECT STR_TO_DATE("March 16 2022", "%M %e %Y");
Example 2: MySQL STR_TO_DATE() Function

MySQL STR_TO_DATE() Function Example 3

SELECT STR_TO_DATE("100212", "%h %i %f");
Example 3: MySQL STR_TO_DATE() Function


Comments and Discussions!

Load comments ↻





Copyright © 2024 www.includehelp.com. All rights reserved.