mysql str_to_date在子查询中不起作用

发布于 2025-01-27 20:05:16 字数 1906 浏览 4 评论 0原文

如果可以将varchar的列类型转换为日期，我正在尝试检查MySQL。如果没有，则应将DB_ID存储在临时表中。当我执行查询时，一切正常。但是，一旦我将其与创建临时表相结合，子查询就会出错。

-- doesn't work
DROP TEMPORARY TABLE IF EXISTS my_temp_id_table;
CREATE TEMPORARY TABLE my_temp_id_table(
SELECT DB_ID from verkehrsweg v where DB_ID in (
    SELECT DB_ID 
    FROM verkehrsweg v1 
    where STR_TO_DATE(v1.LASTUPDATEDBY, GET_FORMAT(DATE,'ISO')) is null) 
and LASTUPDATEDBY is not null );

-- works fine
SELECT DB_ID 
from verkehrsweg v 
where DB_ID in (
    SELECT DB_ID 
    FROM verkehrsweg v1 
    where STR_TO_DATE(v1.LASTUPDATEDBY, GET_FORMAT(DATE,'ISO')) is null) 
and LASTUPDATEDBY is not null ;

错误消息：

错误代码：1411。不正确的DateTime值：foruction str_to_date的'HALLO'

来自文档： str_to_date（str，格式）

这是date_format（）函数的倒数。它采用字符串str和格式字符串格式。 str_to_date（）如果格式字符串包含日期和时间部分，或者如果字符串仅包含日期或时间零件，则返回DateTime值。如果从str提取的日期，时间或日期值值是非法的，则str_to_date（）返回null并产生警告。

编辑：我的桌子具有以下结构。

<table>
  <tr>
    <th>DB_ID (BIGINT)</th>
    <th>DATECREATED (DATETIME)</th>
    <th>DATEUPDATED (DATETIME)</th>
    <th>LASTUPDATEDBY (VARCHAR 255)</th>
  </tr>
  <tr>
    <td>1</td>
    <td>null</td>
    <td>null</td>
    <td>2022-12-15</td>
  </tr>
   <tr>
    <td>2</td>
    <td>null</td>
    <td>null</td>
    <td>null</td>
  </tr>
   <tr>
    <td>3</td>
    <td>null</td>
    <td>null</td>
    <td>hallo</td>
  </tr>
   <tr>
    <td>4</td>
    <td>null</td>
    <td>null</td>
    <td>null</td>
  </tr>
</table>

原文

I am trying to check in MySQL if I can convert the column type from varchar to date. If not, the DB_IDs should be stored in a temporary table. When I execute the query, everything works fine. However, as soon as I combine it with creating the temporary table, the subquery fails with an error.

-- doesn't work
DROP TEMPORARY TABLE IF EXISTS my_temp_id_table;
CREATE TEMPORARY TABLE my_temp_id_table(
SELECT DB_ID from verkehrsweg v where DB_ID in (
    SELECT DB_ID 
    FROM verkehrsweg v1 
    where STR_TO_DATE(v1.LASTUPDATEDBY, GET_FORMAT(DATE,'ISO')) is null) 
and LASTUPDATEDBY is not null );

-- works fine
SELECT DB_ID 
from verkehrsweg v 
where DB_ID in (
    SELECT DB_ID 
    FROM verkehrsweg v1 
    where STR_TO_DATE(v1.LASTUPDATEDBY, GET_FORMAT(DATE,'ISO')) is null) 
and LASTUPDATEDBY is not null ;

Error Message:

Error Code: 1411. Incorrect datetime value: 'Hallo' for function str_to_date

From documentation:
STR_TO_DATE(str,format)

This is the inverse of the DATE_FORMAT() function. It takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts. If the date, time, or datetime value extracted from str is illegal, STR_TO_DATE() returns NULL and produces a warning.

EDIT:
My table has the following structure.

<table>
  <tr>
    <th>DB_ID (BIGINT)</th>
    <th>DATECREATED (DATETIME)</th>
    <th>DATEUPDATED (DATETIME)</th>
    <th>LASTUPDATEDBY (VARCHAR 255)</th>
  </tr>
  <tr>
    <td>1</td>
    <td>null</td>
    <td>null</td>
    <td>2022-12-15</td>
  </tr>
   <tr>
    <td>2</td>
    <td>null</td>
    <td>null</td>
    <td>null</td>
  </tr>
   <tr>
    <td>3</td>
    <td>null</td>
    <td>null</td>
    <td>hallo</td>
  </tr>
   <tr>
    <td>4</td>
    <td>null</td>
    <td>null</td>
    <td>null</td>
  </tr>
</table>

分享到QQ

分享到微博