SQL 中日期转换是如何工作的？

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

导航至

处理日期和时间对于诸如时间戳记录、管理当前事件、保存历史记录以及跟踪事件等任务至关重要。然而，由于数据不匹配或格式不符合要求，或查询输出不符合需求等原因，处理日期和时间可能会变得复杂。幸运的是，有一些解决方案。一种方法是在数据进入您的 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 表示您的日期将以月份名称（一月……十二月）、月中的日期（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日期转换过程中有一些常见的错误需要避免。

  • 日期格式错误：这种错误主要发生在日期格式在不同国家之间不同的情况下。因此，一个日期和时间为“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;
    Invalid or non-existent 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服务器配置或设计为使用特定的日期和时间格式（如英语日期）的情况下。在这种情况下，特定的文化日期格式可能与SQL服务器不兼容，除非您修改服务器设置。因此，如果您的SQL服务器配置为英语日期和时间，它将只接受该格式的输入。一种解决方案是使用全球标准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非常广泛。您可以从SQL和其内置函数继续学习，并探索InfluxData博客，以及InfluxDB Cloud界面，它支持原生SQL查询。新的InfluxDB云存储引擎还提供了Flight SQL支持，使您能够为项目利用第三方SQL工具。

其他资源

本文由Ifeanyi Benedict Iheagwara撰写。Ifeanyi是一位数据分析师和Power Platform开发者，热衷于技术写作、为开源组织贡献力量以及建设社区。Ifeanyi撰写有关机器学习、数据科学和DevOps的文章，并乐于以任何方式为开源项目和全球生态系统做出贡献。