SQL 中日期转换如何工作？

作者：社区 / 开发者
2023年10月06日

导航至

处理日期和时间对于诸如时间戳条目、管理当前事件、保留历史记录和跟踪事件等任务至关重要。但是，处理日期和时间可能很棘手，原因有很多，例如数据不匹配或格式不正确，或者查询输出与您的需求不符。幸运的是，有解决方案。一种方法是确保数据格式在进入 SQL 数据库解决方案之前是正确的。不幸的是，您并非总是可以控制这一点。但是，另一种解决方案是利用 SQL Server 的内置日期和时间函数来简化您的日期和时间格式化的复杂性。

这篇文章将通过深入了解 SQL 日期转换的基础知识以及您可以使用的各种方法来探讨 SQL Server 中的日期和时间转换。我们还将提供简短的分步教程，查看一些案例研究，并以一些最佳实践结束。

SQL 日期转换的基础知识

在深入研究各种 SQL 内置功能之前，重要的是您要了解 SQL 的各种日期和时间数据类型和格式。这种基础理解将为使用 SQL 进行有效的日期操作和转换奠定基础。

在 SQL Server 中，数据以日期或日期/时间值的形式存储，共有五种不同的格式

  • DATE，格式为 YYYY-MM-DD。
  • TIME，格式为 hh:mm:ss。
  • DATETIME，表示日期和时间，格式为 YYYY-MM-DD HH:MI:SS。
  • TIMESTAMP，格式为 YYYY-MM-DD HH:MI:SS。
  • YEAR，是 YYYY 的四位数字格式。

除了这些日期函数之外，SQL Server 还具有内置的日期函数，可让您获取日期。其中一些包括以下内容

  • GETDATE() 获取当前日期和时间。
SELECT GETDATE();

select get date

  • CURRENT_TIMESTAMP 返回系统中的当前时间戳。
SELECT CURRENT_TIMESTAMP;
  • SYSDATETIME() 返回服务器的日期和时间。
Select SYSDATETIME();
  • SYSDATETIMEOffset() 获取服务器的日期、时间和 UTC 偏移量。
Select SYSDATETIMEOffset();
  • DATEDIFF (date_part, start_date, end_date) 用于查找两个日期之间的差值。
SELECT DATEDIFF(month, '2023-01-31 23:59:59', '2023-05-01 00:00:00');
  • DATEADD (date_part, number, date) 用于将数字添加到给定日期。例如，您可以使用此语法将 1 添加到日期的月份值。
SELECT DATEADD(month, 1, '2023-08-10');

您还应该查看这篇关于 SQL 日期和时间函数以及如何获取当前日期 的文章，以获得更详细和全面的理解。

SQL 日期转换的不同方法

现在您已经了解了各种日期数据类型，我们可以继续介绍可用于 SQL Server 中日期转换的函数。以下是一些在 SQL 中转换数据的各种方法。

CAST

CAST 是一个内置的 SQL 转换函数，可将值从一种数据类型转换为另一种数据类型。您可以使用此函数将字符串转换为日期或从 DATETIME 中提取日期。

CAST 函数的语法如下

CAST (expression AS [data type])

假设您想将字符串 ‘12-12-2023’ 更改为日期。

SELECT CAST('12-12-2023' AS date);

同样，您也可以使用 CAST 从当前日期和时间中提取日期。

SELECT CAST(getdate() AS date);

CONVERT

另一个函数是 CONVERT，这是一个内置函数，允许用户将表达式从一种数据类型转换为另一种数据类型。因此，您也可以使用它从各种数据类型中提取数据。

CONVERT 函数的语法如下

CONVERT(data_type(length), expression, style)

与 CAST 函数类似，您也可以使用 CONVERT 从当前日期和时间中提取日期。

SELECT CONVERT(date, getdate());

您可以从 DATETIME 值中提取日期。

SELECT CONVERT(VARCHAR(10), getdate(), 112);

上面语法中使用的“112”指的是 ISO 标准日期格式。这种 112 样式将以 yyyy/mm/dd 格式返回。

DATE_FORMAT

DATE_FORMAT 函数将日期格式化为指定的格式。

DATE_FORMAT 函数的语法如下

DATE_FORMAT(date, format)

让我们看看它是如何工作的。

SELECT DATE_FORMAT("2017-06-15", "%M %d %Y");

%M、%d 和 %Y 表示指定符，用于确定日期值的格式。在本例中，%M、%d 和 %Y 表示您的日期将显示月份名称（January…December）、数字形式的月份中的日期 (00…31) 和四位数字的年份。

MySQL 文档 重点介绍了所有可用的指定符。

FORMAT

SQL FORMAT 函数使用指定的格式和可选的区域性来格式化您的值。如果您未指定任何区域性，SQL Server 将使用当前会话的语言。

FORMAT 函数的语法如下

FORMAT(value, format, culture)

这是一个示例

DECLARE @d DATETIME ='2020-12-08 16:36:17.760';

SELECT FORMAT (@d, 'd', 'en-US') AS 'US English',
FORMAT (@d, 'D',  'de-de' ) AS 'German',
FORMAT(@d, 'f', 'kn-IN' )  AS 'Kannada',
FORMAT(@d, 'F', 'en-gb') AS 'Great Britain English';

日期转换函数的用例

现在让我们看看使用您刚刚学习的函数的一些实际用例。

如何在 SQL 中将日期转换为日？

对于这种情况，您可以使用 FORMAT 函数从日期值中获取日。

SELECT GETDATE() 'Today Date', FORMAT(GETDATE(),'dddd') 'Date as Day'

select today date

如何在 SQL 中将日期转换为年？

另一种情况是尝试从日期值中获取年。让我们尝试 CONVERT 函数；您可以使用我们之前学习的其他函数轻松完成此操作。

SELECT GETDATE() 'Today Date', CONVERT(VARCHAR(4), getdate(), 120) 'Date as Year';

convert year

如果您正在寻找一个功能强大的 SQL 客户端和数据库管理器来快速构建和扩展基于时间序列的应用程序，那么您绝对应该尝试 InfluxDB。

SQL 日期转换期间要避免的常见错误

在日期转换方面，有时会出现日期错误，例如“将日期和/或时间从字符字符串转换为日期和/或时间失败”或“日期格式不正确”错误。如果您想避免这些错误，则需要避免在 SQL 日期转换期间犯一些常见错误。

  • 日期格式不正确： 此错误的主要原因是国家/地区的日期格式不同。因此，对于“DD/MM/YYYY”格式，日期和时间为“08/15/2023 23:20:30”的 SQL 数据库可能会被读取为无效，因为公历中没有“15”月。
    --Wrong format
Declare @date_time_value varchar(100)= '08/15/2023 23:20:30'
select CONVERT(datetime2, @date_time_value, 103) as Date;
    Incorrect date format 
    --Correct format
Declare @date_time_value varchar(100)= '15/08/2023 23:20:30'
select CONVERT(datetime2, @date_time_value, 103) as Date;
    Declare date time value
  • 无效或不存在的日期： 可能会抛出错误，因为尝试存储不存在的日期。
    --Wrong format
Declare @date_time_value varchar(100)= '30/02/2023 23:20:30'
select CONVERT(datetime2, @date_time_value, 103) as Date;
    无效或不存在的日期 抛出此错误是因为二月份没有 30 日。
    --Correct format
Declare @date_time_value varchar(100)= '28/02/2023 23:20:30'
select CONVERT(datetime2, @date_time_value, 103) as Date;
    Invalid or non-existent date
  • 拼写错误和特定于区域性的日期/时间文字： 如果您的 SQL 语法包含拼写错误的名称，或者您的 SQL Server 配置或设计为使用特定的日期和时间格式（例如英语日期），则可能会发生这种情况。在这种情况下，除非您修改服务器设置，否则特定于区域性的日期格式可能与 SQL Server 不兼容。因此，如果您的 SQL Server 配置为英语日期和时间，则它将仅接受该格式的输入。解决此问题的方法是使用全球标准 ISO 8601“YYYY-MM-DDThh: mm: ss”格式，该格式与语言无关，并且几乎所有数据库系统都认可。
    SELECT CAST('2023 Septemeber 11' AS DATETIME);
    Misspelled and culture-specific date

SQL 日期转换的最佳实践

为了避免前面提到的常见错误，您可以遵循一些最佳实践来有效地处理 SQL 数据库中的日期转换。这些实践很重要，因为它们不仅保证了跨各种系统的兼容性，而且还会对您的数据准确性和查询性能产生重大影响。

  • 避免以特定于区域性或区域格式编写日期，因为它可能意味着不同的日期，具体取决于谁在查看。坚持使用全球标准 ISO 8601 格式将是更好的方法。

  • 使用比较运算符（如 >= 和 <=）而不是 BETWEEN。虽然 BETWEEN 非常适合整数，但它可能会造成一些日期歧义，尤其是在近距离数据方面。比较运算符具有包容性，并且在处理存储具有毫秒或更精确时间分量的时间戳的数据库时，您可以指定您感兴趣的确切范围，从而为您提供更多控制。此外，它们还提高了各种 SQL 系统之间的一致性。

  • 始终将日期值存储在 DATE、DATETIME、DATETIMEOFFSET 或最好是 DATETIME2 中，而不是文本数据类型（VARCHAR、CHAR、NVARCHAR、TEXT 或 CHAR）中，因为它们提供更高的精度。

结论

总而言之，让我们回顾一下这篇文章中涵盖的所有内容。

首先，我们介绍了 SQL 日期转换的要点。我们首先强调了理解日期数据类型的重要性。然后，我们向您介绍了 SQL 中转换日期的不同方法，然后深入研究了一些实际示例。从那里，我们查看了一些要避免的常见错误，并重点介绍了一些在日期转换期间要遵循的最佳实践。

然而，您与 SQL 及其内置函数 的旅程并未到此结束——SQL 非常广阔。您可以继续从 InfluxData 博客 中学习，并探索支持本机 SQL 查询的 InfluxDB Cloud 界面。新的 InfluxDB 云存储引擎还提供 Flight SQL 支持，使您能够为您的项目利用第三方 SQL 工具。

其他资源

这篇文章由 Ifeanyi Benedict Iheagwara 撰写。Ifeanyi 是一位数据分析师和 Power Platform 开发人员，他对技术写作、为开源组织做贡献和建立社区充满热情。Ifeanyi 撰写有关机器学习、数据科学和 DevOps 的文章，并喜欢以任何身份为开源项目和全球生态系统做出贡献。