转换“值"从长文本列到日期格式 [英] Converting "Values" from long text column to date format

问题描述

MySQL 表有一个长文本列，其中包含许多不同格式的不同条目.我想将特定类型的条目转换为 mm-dd-yy 的标准日期格式.

The MySQL table has a longtext Column that houses a number of different entries of different formats. I would like to convert a particular type of entries into a standard date format of mm-dd-yy.

这是我用来透视表并仅提取我需要的值的查询.AS 日期值是我试图转换为日期的值.我相当确定我只需要使用 date_format(%m-%d-%y) 但我不确定如何以及在哪里输入它.

Here is the query that I am using to pivot the table and pull out just the values that I need. The AS date values are the ones that I am trying to convert to a date. I am fairly sure I just need to use date_format(%m-%d-%y) but I am not sure how and where to enter it.

选择 * 从 (选择条目_id，

SELECT * FROM ( SELECT entry_id,

GROUP_CONCAT(IF(field_id='fld_5076430', value,'') SEPARATOR '') 作为用户，

GROUP_CONCAT(IF(field_id='fld_5076430', value,'') SEPARATOR '') AS user,

GROUP_CONCAT(IF(field_id='fld_8768091', value,'') SEPARATOR '') AS 首先，

GROUP_CONCAT(IF(field_id='fld_8768091', value,'') SEPARATOR '') AS first,

GROUP_CONCAT(IF(field_id='fld_9970286', value,'') SEPARATOR '') 最后，

GROUP_CONCAT(IF(field_id='fld_9970286', value,'') SEPARATOR '') AS last,

GROUP_CONCAT(IF(field_id='fld_6009157', value,'') SEPARATOR '') AS 日期，

GROUP_CONCAT(IF(field_id='fld_6009157', value,'') SEPARATOR '') AS date,

GROUP_CONCAT(IF(field_id='fld_5105414', value,'') SEPARATOR '') AS 事件，

GROUP_CONCAT(IF(field_id='fld_5105414', value,'') SEPARATOR '') AS event,

GROUP_CONCAT(IF(field_id='fld_7723931', value,'') SEPARATOR '') AS 单位，

GROUP_CONCAT(IF(field_id='fld_7723931', value,'') SEPARATOR '') AS unit,

GROUP_CONCAT(IF(field_id='fld_1521970', value,'') SEPARATOR '') AS 工作，

GROUP_CONCAT(IF(field_id='fld_1521970', value,'') SEPARATOR '') AS work,

GROUP_CONCAT(IF(field_id='fld_4590434', value,'') SEPARATOR '') AS 小时

GROUP_CONCAT(IF(field_id='fld_4590434', value,'') SEPARATOR '') AS hours

来自 kcdte_cf_form_entry_values

FROM kcdte_cf_form_entry_values

GROUP BY entry_id) AS 行WHERE hours > '' AND last > '';

GROUP BY entry_id) AS rows WHERE hours > '' AND last > '';

我要感谢来自这个小组的 Michael K，他帮助我完成了这个项目.

I would like to thank Michael K from this group for his help in getting me where I am on this project.

推荐答案

您可以尝试使用 MySQL 函数 STR_TO_DATE() 将字符串转换为日期格式:

You could try using the MySQL-function STR_TO_DATE() to convert your strings to date format:

STR_TO_DATE(GROUP_CONCAT(IF(field_id='fld_6009157', value,'') SEPARATOR ''), '%m-%d-%Y') AS date,

如果您打算将日期存储在 MySQL 数据库中，数据类型为 date，则应使用 ' 实现的格式 YYYY-mm-dd%Y-%m-%d' 而不是上面例子中的 '%m-%d-%Y'.

If you are planning to store the dates in a MySQL-database with the datatype date you should use the format YYYY-mm-dd achieved with '%Y-%m-%d' instead of '%m-%d-%Y' in the above example.

这篇关于转换“值"从长文本列到日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！