Блог

У меня, вероятно, очень простая проблема. Тем не менее, я провел обширные исследования и пока не нашел решения. В принципе, я хочу преобразовать измененную строковую переменную в формат ДАТЫ (в частности %Y ).

У меня есть переменная столбца с именем dob , которая включает даты в VARCHAR формате. Значения этих строк различаются и могут выглядеть как любое из следующих: 01 JAN 1900 , ABT 1960 или Unknown . Тем не менее, год всегда состоит из последних четырех цифр, поэтому я беру год, создавая подстроку. Но я хочу преобразовать эту подстроку в YEAR формат. Я думаю, что мне нужно использовать str_to_date для достижения этой цели.

Это мой запрос MySQL:

 SELECT dob, STR_TO_DATE(SUBSTRING(dob, -4), "%Y") as YEAR
FROM person_table;
  

После ее запуска я получаю только NULL значения. Я чего-то не понимаю?

Вот мои спецификации MySQL:

 innodb_version: 5.7.20
protocol_version: 10
  

Спасибо за вашу помощь!

Редактировать: предоставление информации о режиме SQL:

  --------------- -----------------------------------------------------------  
| Variable_name | Value |
  -------------- ----------------------------------------------------------- 
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
 --------------- ------------------------------------------------------------
1 row in set (0.00 sec)
  

После выполнения запроса SELECT STR_TO_DATE('2009','%Y'); я получаю NULL следующее предупреждение:

 I get the following warning:
 --------- ------ ----------------------------------------------------------- 
| Level   | Code | Message |
 --------- ------ ----------------------------------------------------------- 
| Warning | 1411 | Incorrect datetime value: '2009' for function str_to_date |
 --------- ------ ----------------------------------------------------------- 
1 row in set (0.00 sec)
  

str_to_date возвращает date тип, и только год является неполной датой. str_to_date неполные части будут заполнены нулями, если у вас не включен no_zero_dates режим. Это часть строгого режима SQL, который используется по умолчанию в MySQL 8.0; это позволяет избежать худших из причуд MySQL.

Строгий режим управляет тем, как MySQL обрабатывает недопустимые или отсутствующие значения в операторах изменения данных, таких как INSERT или UPDATE. Значение может быть недопустимым по нескольким причинам. Например, у него может быть неправильный тип данных для столбца, или он может находиться вне диапазона

Без строгого режима SQL MySQL превратит «2009» в недопустимую дату 2009-00-00.

 mysql> set sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT STR_TO_DATE('2009','%Y');
 -------------------------- 
| STR_TO_DATE('2009','%Y') |
 -------------------------- 
| 2009-00-00               |
 -------------------------- 
1 row in set (0.00 sec)
  

В строгом режиме SQL этого не будет.

 mysql> show variables like 'sql_mode';
 --------------- ----------------------------------------------------------------------------------------------------------------------- 
| Variable_name | Value                                                                                                                 |
 --------------- ----------------------------------------------------------------------------------------------------------------------- 
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
 --------------- ----------------------------------------------------------------------------------------------------------------------- 
1 row in set (0.00 sec)

mysql> SELECT STR_TO_DATE('2009','%Y');
 -------------------------- 
| STR_TO_DATE('2009','%Y') |
 -------------------------- 
| NULL                     |
 -------------------------- 
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
 --------- ------ ----------------------------------------------------------- 
| Level   | Code | Message                                                   |
 --------- ------ ----------------------------------------------------------- 
| Warning | 1411 | Incorrect datetime value: '2009' for function str_to_date |
 --------- ------ ----------------------------------------------------------- 
1 row in set (0.00 sec)
  

Чтобы решить вашу проблему, вместо того, чтобы пытаться выполнить универсальное преобразование, я бы рекомендовал попробовать несколько форматов от наиболее до наименее специфичного использования coalesce . Вам нужно будет добавить недостающие части даты по мере необходимости.

 select coalesce(
  str_to_date(dob, '%d %b %Y'),
  str_to_date(concat(dob, '-01-01'), 'ABT %Y-%m-%d')
)
from person_table;
  

Поскольку это очень некрасиво делать, я бы также рекомендовал добавить правильный date столбец и выполнить update преобразование из беспорядочной строки dates в правильные date s. Затем запрашивайте новый столбец в будущем.

 alter table person_table add column dob_date date;

update person_table
set dob_date = coalesce(
  str_to_date(dob, '%d %b %Y'),
  str_to_date(concat(dob, '-01-01'), 'ABT %Y-%m-%d')
)
where dob_date is null;
  

Затем вы можете проверить наличие людей с нулевым значением dob_date , изучить их dob поле и адаптировать свое преобразование. Повторяйте по мере необходимости.

Обновить

Чтобы добавить, да, мне нужен 2020 год, а не строка. Причина в том, что мне нужно сравнить значения года.

Как строки они не будут сравниваться так, как вам нужно. Строки сравниваются символ за символом. '200' больше, чем строка '1999' .

 mysql> select '1999' < '2000';
 ----------------- 
| '1999' < '2000' |
 ----------------- 
|               1 |
 ----------------- 
1 row in set (0.00 sec)

mysql> select '1999' < '200';
 ---------------- 
| '1999' < '200' |
 ---------------- 
|              1 |
 ---------------- 
1 row in set (0.00 sec)
  

Вам нужно преобразовать их в целые числа со знаком.

 mysql> select cast("1999" as signed) < cast('2000' as signed);
 ------------------------------------------------- 
| cast("1999" as signed) < cast('2000' as signed) |
 ------------------------------------------------- 
|                                               1 |
 ------------------------------------------------- 
1 row in set (0.00 sec)

mysql> select cast("1999" as signed) < cast('200' as signed);
 ------------------------------------------------ 
| cast("1999" as signed) < cast('200' as signed) |
 ------------------------------------------------ 
|                                              0 |
 ------------------------------------------------ 
1 row in set (0.01 sec)
  

Итак, ваш запрос будет…

 select dob, cast(substring(dob, -4) as signed) as year
from person_table;