STR_TO_DATE(str,format)

MySQL 8.4 🇮🇩 Bahasa Indonesia

Ini adalah kebalikan dari fungsi DATE_FORMAT(). Dibutuhkan string str dan format string format. STR_TO_DATE() mengembalikan nilai DATETIME jika string format berisi bagian tanggal dan waktu, atau nilai DATE atau TIME jika string hanya berisi bagian tanggal atau waktu. Jika str atau formatnya NULL, fungsinya mengembalikan NULL. Jika nilai tanggal, waktu, atau waktu yang diekstraksi dari str tidak dapat diuraikan sesuai dengan aturan yang diikuti oleh server, STR_TO_DATE() mengembalikan NULL dan menghasilkan peringatan.

Syntax

MYSQL

SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');
'2013-05-01'
SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y');
'2013-05-01'

Contoh

Example

The server scans str attempting to match format to it. The format string can contain literal characters and format specifiers beginning with %. Literal characters in format must match literally in str. Format specifiers in format must match a date or time part in str. For the specifiers that can be used in format, see the DATE_FORMAT() function description.

MYSQL

SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');
'2013-05-01'
SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y');
'2013-05-01'

Example 2

Scanning starts at the beginning of str and fails if format is found not to match. Extra characters at the end of str are ignored.

MYSQL

SELECT STR_TO_DATE('a09:30:17','a%h:%i:%s');
'09:30:17'
SELECT STR_TO_DATE('a09:30:17','%h:%i:%s');
NULL
SELECT STR_TO_DATE('09:30:17a','%h:%i:%s');
'09:30:17'

Example 3

Unspecified date or time parts have a value of 0, so incompletely specified values in str produce a result with some or all parts set to 0:

MYSQL

SELECT STR_TO_DATE('abc','abc');
'0000-00-00'
SELECT STR_TO_DATE('9','%m');
'0000-09-00'
SELECT STR_TO_DATE('9','%s');
'00:00:09'

Example 4

Range checking on the parts of date values is as described in Section 13.2.2, “The DATE, DATETIME, and TIMESTAMP Types”. This means, for example, that “zero” dates or dates with part values of 0 are permitted unless the SQL mode is set to disallow such values.

MYSQL

SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');
'0000-00-00'
SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
'2004-04-31'

Example 5

If the NO_ZERO_DATE SQL mode is enabled, zero dates are disallowed. In that case, STR_TO_DATE() returns NULL and generates a warning:

MYSQL

SET sql_mode = '';
SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');
+---------------------------------------+
| STR_TO_DATE('00/00/0000', '%m/%d/%Y') |
+---------------------------------------+
| 0000-00-00                            |
+---------------------------------------+
SET sql_mode = 'NO_ZERO_DATE';
SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');
+---------------------------------------+
| STR_TO_DATE('00/00/0000', '%m/%d/%Y') |
+---------------------------------------+
| NULL                                  |
+---------------------------------------+
SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1411
Message: Incorrect datetime value: '00/00/0000' for function str_to_date

Example 6

In some previous versions of MySQL, it was possible to pass an invalid date string such as '2021-11-31' to this function. In MySQL 8.4, STR_TO_DATE() performs complete range checking and raises an error if the date after conversion would be invalid.

MYSQL

SELECT STR_TO_DATE('200442 Monday', '%X%V %W');
'2004-10-18'