Mysql STR TO DATE
From w3cyberlearnings
Contents |
MySQL STR_TO_DATE Function
This function is the reverse of the DATE_FORMAT() function. This function is scanning the str to match with the format. It returns date field when the format and the str are supported, and it returns null otherwise.
Syntax STR_TO_DATE
- str: time, date, or datetime string representation.
- format:
- %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
- %u - Week (00..53), where Monday is the first day of the week
- %V - Week (01..53), where Sunday is the first day of the week; used with **%X
- %v - Week (01..53), where Monday is the first day of the week; 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
- %x - x, for any “x” not listed above
STR_TO_DATE(str, format);
Example 1
mysql> SELECT STR_TO_DATE('02,05,2013','%d, %m, %Y');
+----------------------------------------+
| STR_TO_DATE('02,05,2013','%d, %m, %Y') |
+----------------------------------------+
| 2013-05-02 |
+----------------------------------------+
1 row in set (0.01 sec)
Example 2
mysql> SELECT STR_TO_DATE('april/02/2012','%M/%d/%Y');
+-----------------------------------------+
| STR_TO_DATE('april/02/2012','%M/%d/%Y') |
+-----------------------------------------+
| 2012-04-02 |
+-----------------------------------------+
1 row in set (0.00 sec)
Example 3
mysql> SELECT STR_TO_DATE('2/april/2012','%d/%M/%Y');
+----------------------------------------+
| STR_TO_DATE('2/april/2012','%d/%M/%Y') |
+----------------------------------------+
| 2012-04-02 |
+----------------------------------------+
1 row in set (0.01 sec)
Example 4
mysql> SELECT STR_TO_DATE('2/april/2012 21:44:20','%d/%M/%Y %H:%i:%S');
+----------------------------------------------------------+
| STR_TO_DATE('2/april/2012 21:44:20','%d/%M/%Y %H:%i:%S') |
+----------------------------------------------------------+
| 2012-04-02 21:44:20 |
+----------------------------------------------------------+
1 row in set (0.00 sec)
