SQL日期格式转换yyyy-mm-dd|极客笔记

SQL日期格式转换yyyy-mm-dd

介绍

在数据库中，日期类型的数据是非常常见的。不同的数据库管理系统（DBMS）可能会使用不同的日期格式进行存储和显示。而在开发应用程序的过程中，我们经常需要进行日期格式的转换，以方便处理和展示日期数据。

本文将详细介绍在SQL中如何进行日期格式的转换，以及一些常用的日期函数和示例代码。

日期类型

在SQL中，常见的日期类型有以下几种：

DATE：表示日期，格式为yyyy-mm-dd。
TIME：表示时间，格式为hh:mm:ss。
DATETIME 或 TIMESTAMP：表示日期和时间，格式为yyyy-mm-dd hh:mm:ss。
YEAR：表示年份，格式为yyyy。

不同的数据库管理系统支持的日期类型可能略有不同，但大致上都会有类似的概念。

日期格式转换

在不同的数据库管理系统中，日期数据的存储和显示格式可能不同。为了方便处理和展示日期数据，我们有时需要对日期进行格式转换。

下面是一些常见的日期格式转换函数：

DATE_FORMAT()

DATE_FORMAT(date, format) 函数用于将日期按照指定的格式进行格式化。其中，date 参数为要格式化的日期，format 参数为指定的格式。

以下是一些常用的格式化选项：

%Y：四位数的年份（例如：2022）。
%y：两位数的年份（例如：22）。
%m：两位数的月份（01到12）。
%d：两位数的天数（01到31）。
%H：24小时制的小时数（00到23）。
%h：12小时制的小时数（01到12）。
%i：两位数的分钟数（00到59）。
%s：两位数的秒数（00到59）。

下面是一个示例，将日期格式从yyyy-mm-dd转换为mm/dd/yyyy：

SELECT DATE_FORMAT('2022-05-15', '%m/%d/%Y') AS new_date;

运行结果：

new_date
-----------
05/15/2022

CAST()

CAST(expr AS type) 函数用于将表达式 expr 转换为指定的类型 type。

对于日期类型的转换，可以使用 CAST() 来将字符串转换为日期类型，或者将日期类型转换为字符串。

下面是一个将字符串转换为日期类型的示例：

SELECT CAST('2022-05-15' AS DATE) AS new_date;

运行结果：

new_date
-----------
2022-05-15

下面是一个将日期类型转换为字符串的示例：

SELECT CAST('2022-05-15' AS CHAR) AS new_date;

运行结果：

new_date
-----------
2022-05-15

STR_TO_DATE()

STR_TO_DATE(str, format) 函数用于将字符串按照指定的格式转换为日期。其中，str 参数为要转换的字符串，format 参数为指定的格式。

下面是一个将字符串转换为日期类型的示例：

SELECT STR_TO_DATE('05-15-2022', '%m-%d-%Y') AS new_date;

运行结果：

new_date
-----------
2022-05-15

日期计算和比较

在SQL中，我们可以对日期进行各种计算和比较操作，以满足不同的需求。

日期加减

可以使用 DATE_ADD(date, INTERVAL expr unit) 函数来对日期进行加法操作，或使用 DATE_SUB(date, INTERVAL expr unit) 函数来对日期进行减法操作。

其中，date 参数为要进行计算的日期，expr 参数为要加/减的值，unit 参数为单位。

以下是一些常用的单位选项：

YEAR：年份。
MONTH：月份。
DAY：天数。
HOUR：小时数。
MINUTE：分钟数。
SECOND：秒数。

下面是一个将当前日期加一天的示例：

SELECT DATE_ADD(CURDATE(), INTERVAL 1 DAY) AS new_date;

运行结果：

new_date
-----------
2022-10-14

日期比较

可以使用比较运算符（如等于、大于、小于等）来比较日期的大小。

下面是一个比较日期的示例，检查 start_date 是否在 end_date 之前：

SELECT * FROM table_name WHERE start_date < end_date;

示例代码

下面是一个完整的示例代码，展示了如何在MySQL中进行日期格式转换和计算：

-- 创建测试表
CREATE TABLE test_dates (
    id INT PRIMARY KEY,
    start_date DATE,
    end_date DATE
);

-- 插入测试数据
INSERT INTO test_dates VALUES
    (1, '2022-01-01', '2022-12-31'),
    (2, '2022-03-15', '2022-03-20'),
    (3, '2022-06-01', '2022-06-30');

-- 查询结果并进行日期格式转换
SELECT
    id,
    DATE_FORMAT(start_date, '%Y-%m-%d') AS formatted_start_date,
    DATE_FORMAT(end_date, '%Y-%m-%d') AS formatted_end_date
FROM
    test_dates;

-- 查询持续时间
SELECT
    id,
    DATEDIFF(end_date, start_date) + 1 AS duration_days
FROM
    test_dates;

运行结果：

id | formatted_start_date | formatted_end_date
---|---------------------|-------------------
1  | 2022-01-01          | 2022-12-31
2  | 2022-03-15          | 2022-03-20
3  | 2022-06-01          | 2022-06-30

id | duration_days
---|--------------
1  | 365
2  | 6
3  | 30