search
Search
Publish
menu
menu search toc more_vert
Robocat
Guest 0reps
Thanks for the thanks!
close
Comments
Log in or sign up
Cancel
Post
account_circle
Profile
exit_to_app
Sign out
help Ask a question
Share on Twitter
search
keyboard_voice
close
Searching Tips
Search for a recipe: "Creating a table in MySQL"
Search for an API documentation: "@append"
Search for code: "!dataframe"
Apply a tag filter: "#python"
Useful Shortcuts
/ to open search panel
Esc to close search panel
to navigate between search results
d to clear all current filters
Enter to expand content preview
icon_star
Doc Search
icon_star
Code Search Beta
SORRY NOTHING FOUND!
mic
Start speaking...
Voice search is only supported in Safari and Chrome.
Navigate to
A
A
share
thumb_up_alt
bookmark
arrow_backShare
Twitter
Facebook

MySQL | STR_TO_DATE method

Database
chevron_right
MySQL
chevron_right
Documentation
chevron_right
Functions
chevron_right
Date and Time
schedule Mar 10, 2022
Last updated
local_offer MySQL
Tags

MySQL's STR_TO_DATE(~) method will return a date, time or datetime value from a string and its format.

Parameters

1. string | string

The string to be converted to a date, time or datetime.

2. format | string

The format of the string argument.

Return value

A date, time or datetime based on the input string.

Examples

Basic usage

To return a date value from the string 'July 5 2020':

SELECT STR_TO_DATE('July 5 2020','%M %d %Y');
+---------------------------------------+
| STR_TO_DATE('July 5 2020','%M %d %Y') |
+---------------------------------------+
| 2020-07-05 |
+---------------------------------------+

If the string does not match the format provided NULL is returned:

SELECT STR_TO_DATE('July 5, 2020','%m %d,%Y');
+----------------------------------------+
| STR_TO_DATE('July 5, 2020','%m %d,%Y') |
+----------------------------------------+
| NULL |
+----------------------------------------+

Here, the format %m means we are expecting a month number rather than month name, which is why NULL is returned.

Correcting the above string to meet the '%m %d,%Y' format:

SELECT STR_TO_DATE('07 5, 2020','%m %d,%Y');
+--------------------------------------+
| STR_TO_DATE('07 5, 2020','%m %d,%Y') |
+--------------------------------------+
| 2020-07-05 |
+--------------------------------------+

List of formatting types

Below is a table with a list of valid formatting types:

Specifier

Description

%a

Abbreviated weekday name (Sun..Sat)

%b

Abbreviated month name (Jan..Dec)

%c

Month, numeric (0..12)

%D

Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)

%d

Day of the month, numeric (00..31)

%e

Day of the month, numeric (0..31)

%f

Microseconds (000000..999999)

%H

Hour (00..23)

%h

Hour (01..12)

%I

Hour (01..12)

%i

Minutes, numeric (00..59)

%j

Day of year (001..366)

%k

Hour (0..23)

%l

Hour (1..12)

%M

Month name (January..December)

%m

Month, numeric (00..12)

%p

AM or PM

%r

Time, 12-hour (hh:mm:ss followed by AM or PM)

%S

Seconds (00..59)

%s

Seconds (00..59)

%T

Time, 24-hour (hh:mm:ss)

%U

Week (00..53), where Sunday is the first day of the week; WEEK() mode 0

%u

Week (00..53), where Monday is the first day of the week; WEEK() mode 1

Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X

%v

Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x

%W

Weekday name (Sunday..Saturday)

%w

Day of the week (0=Sunday..6=Saturday)

%X

Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V

%x

Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v

%Y

Year, numeric, four digits

%y

Year, numeric (two digits)

A literal % character

robocat
Published by Arthur Yanagisawa
Edited by 0 others
Did you find this page useful?
thumb_up
thumb_down
Ask a question or leave a feedback...