SQL日期格式转换|极客教程

SQL日期格式转换

在数据库中，日期和时间型数据是非常常见的数据类型，而不同的数据库中日期时间的格式也各有不同。有时候我们需要在SQL中对日期进行格式的转换，以便于数据的展示或者计算。本文将详细介绍SQL中日期格式的转换，包括日期和时间型数据类型的格式化、日期之间的计算等操作。

日期时间型数据类型

在SQL中，常见的日期时间型数据类型有DATE、TIME、DATETIME和TIMESTAMP等。不同的数据库支持的日期时间型数据类型有所差异，但基本概念是相似的。

DATE: 表示日期，格式为YYYY-MM-DD。
TIME: 表示时间，格式为HH:MM:SS。
DATETIME: 表示日期和时间，格式为YYYY-MM-DD HH:MM:SS。
TIMESTAMP: 类似于DATETIME，但在某些数据库中有特殊的用途，如记录更新时间等。

日期时间格式的转换

日期时间到字符串

在SQL中，我们经常需要将日期时间型数据转换为字符串格式，以便于展示或者导出。在不同的数据库中，日期时间到字符串的格式化函数也有所不同。

在MySQL中，可以使用DATE_FORMAT()函数将日期时间转换为指定格式的字符串。

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS formatted_date;

运行上述SQL语句，可以将当前时间转换为YYYY-MM-DD HH:MM:SS的格式。例如，运行结果为2021-10-01 18:30:00。

在Oracle数据库中，可以使用TO_CHAR()函数将日期时间转换为字符串。

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS formatted_date FROM dual;

运行上述SQL语句，可以将当前时间转换为YYYY-MM-DD HH:MI:SS的格式。例如，运行结果为2021-10-01 18:30:00。

字符串到日期时间

有时候，我们需要将字符串形式的日期时间转换为日期时间型数据。在SQL中，可以使用CAST()或者CONVERT()函数来实现。

在MySQL中，可以使用STR_TO_DATE()函数将字符串转换为日期时间型数据。

SELECT STR_TO_DATE('2021-10-01', '%Y-%m-%d') AS date_time;

运行上述SQL语句，可以将'2021-10-01'字符串转换为日期时间类型。例如，运行结果为2021-10-01 00:00:00。

在Oracle数据库中，可以使用TO_DATE()函数将字符串转换为日期时间型数据。

SELECT TO_DATE('2021-10-01', 'YYYY-MM-DD') AS date_time FROM dual;

运行上述SQL语句，可以将'2021-10-01'字符串转换为日期时间类型。例如，运行结果为2021-10-01 00:00:00。

日期时间格式的转换

有时候，我们需要对日期时间的格式进行转换，比如将YYYY-MM-DD格式的日期转换为MM/DD/YYYY格式。

在MySQL和Oracle中，可以使用DATE_FORMAT()和TO_CHAR()函数来实现日期格式的转换。

-- MySQL
SELECT DATE_FORMAT('2021-10-01', '%m/%d/%Y') AS formatted_date;

-- Oracle
SELECT TO_CHAR(TO_DATE('2021-10-01', 'YYYY-MM-DD'), 'MM/DD/YYYY') AS formatted_date FROM dual;

运行上述SQL语句，可以将'2021-10-01'日期转换为'10/01/2021'格式。

日期时间之间的计算

除了格式转换，我们还可能需要在SQL中进行日期时间之间的计算，比如计算日期相差的天数、添加指定天数等。

在MySQL中，可以使用DATEDIFF()和DATE_ADD()函数来进行日期时间计算。

-- 计算相差天数
SELECT DATEDIFF('2021-10-10', '2021-10-01') AS diff_days;

-- 添加指定天数
SELECT DATE_ADD('2021-10-01', INTERVAL 10 DAY) AS added_date;

运行上述SQL语句，可以分别计算'2021-10-10'与'2021-10-01'相差的天数和在'2021-10-01'基础上添加10天后的日期。

在Oracle数据库中，可以使用TREATEDIFF()和ADD_MONTHS()函数来进行日期时间计算。

-- 计算相差天数
SELECT TRUNC(TO_DATE('2021-10-10', 'YYYY-MM-DD') - TO_DATE('2021-10-01', 'YYYY-MM-DD')) AS diff_days FROM dual;

-- 添加指定天数
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2021-10-01', 'YYYY-MM-DD'), 1), 'YYYY-MM-DD') AS added_date FROM dual;

运行上述SQL语句，可以同样进行相差天数和添加指定天数的计算。