SQL技术问询：从含时间变量中提取日期并转换为DD/MM/YYYY格式

嘿，针对你提出的从travelperiod字段提取日期并转换格式、添加SQL筛选条件的需求，我来给你详细拆解几种主流SQL方言的解决方案：

首先我们需要先将字符串类型的travelperiod（格式示例：Friday, October 01, 2021 12:00 AM）转换为日期类型，再格式化为你想要的DD/MM/YYYY格式。不同数据库的函数略有差异，以下是常见的几种情况：

MySQL/MariaDB #

使用STR_TO_DATE解析原字符串为日期，再用DATE_FORMAT输出目标格式：

SELECT DATE_FORMAT(STR_TO_DATE(travelperiod, '%W, %M %d, %Y %h:%i %p'), '%d/%m/%Y') AS formatted_travel_date
FROM your_table_name;

• 格式符说明：%W匹配星期全称，%M匹配月份全称，%d匹配两位日期，%Y匹配四位年份，%h:%i %p匹配12小时制的时间和AM/PM标识。

SQL Server #

用CONVERT（或TRY_CONVERT避免转换失败报错）将字符串转为日期时间类型，再用FORMAT输出目标格式：

-- 基础写法
SELECT FORMAT(CONVERT(DATETIME, travelperiod, 109), 'dd/MM/yyyy') AS formatted_travel_date
FROM your_table_name;

-- 容错写法（转换失败时返回NULL）
SELECT FORMAT(TRY_CONVERT(DATETIME, travelperiod, 109), 'dd/MM/yyyy') AS formatted_travel_date
FROM your_table_name;

• 格式码109对应SQL Server中"mon dd yyyy hh:mi:ss:mmmAM（或PM）"的格式，可以完美匹配你的输入。

PostgreSQL #

使用TO_DATE解析字符串，再用TO_CHAR转换为目标格式，注意加上FM前缀去除多余空格：

SELECT TO_CHAR(TO_DATE(travelperiod, 'FMDay, FMMonth DD, YYYY HH12:MI AM'), 'DD/MM/YYYY') AS formatted_travel_date
FROM your_table_name;

• FM前缀用于忽略原字符串中月份、日期部分可能存在的多余空格，确保解析准确。

如果要基于提取后的日期进行筛选，建议先将travelperiod转换为日期类型后再进行比较（而非直接用字符串比较），这样更高效且避免格式冲突。以下是筛选示例（以筛选2021年10月的记录为例）：

MySQL/MariaDB #

SELECT *
FROM your_table_name
WHERE DATE(STR_TO_DATE(travelperiod, '%W, %M %d, %Y %h:%i %p')) BETWEEN '2021-10-01' AND '2021-10-31';

SQL Server #

SELECT *
FROM your_table_name
WHERE CONVERT(DATE, travelperiod, 109) BETWEEN '2021-10-01' AND '2021-10-31';

PostgreSQL #

SELECT *
FROM your_table_name
WHERE TO_DATE(travelperiod, 'FMDay, FMMonth DD, YYYY HH12:MI AM') BETWEEN '2021-10-01' AND '2021-10-31';

额外提示 #

• 确保travelperiod字段的所有记录格式都和示例一致，否则转换可能失败。如果存在格式不一致的情况，优先使用带TRY_前缀的函数（如TRY_STR_TO_DATE、TRY_CONVERT）来避免整个查询报错。
• 如果需要频繁进行这类转换和筛选，建议考虑将转换后的日期存储为单独的日期类型字段，提升查询性能。

内容的提问来源于stack exchange，提问作者Lorenzo