返回系统日期和时间的函数

在 SQL Server 中使用函数处理数据

Ana Voicu

Data Engineer

处理日期和时间的常见错误

日期时间格式或模式不一致
算术运算
时区问题

SQL Server 中的时区

本地时区
UTC 时区（协调世界时）

返回操作系统日期和时间的函数

高精度

SYSDATETIME()
SYSUTCDATETIME()
SYSDATETIMEOFFSET()

低精度

GETDATE()
GETUTCDATE()
CURRENT_TIMESTAMP

高精度函数示例

SELECT 
    SYSDATETIME() AS [SYSDATETIME],
    SYSDATETIMEOFFSET() AS [SYSDATETIMEOFFSET],  
    SYSUTCDATETIME() AS [SYSUTCDATETIME];

| [SYSDATETIME]               | [SYSDATETIMEOFFSET]                | [SYSUTCDATETIME]            |
|-----------------------------|------------------------------------|-----------------------------|
| 2019-04-15 00:35:38.8740380 | 2019-04-15 00:35:38.8740380 +03:00 | 2019-04-14 21:35:38.8740380 |

低精度函数示例

SELECT 
    CURRENT_TIMESTAMP AS [CURRENT_TIMESTAMP], 
    GETDATE() AS [GETDATE] ,
    GETUTCDATE() AS [GETUTCDATE];

| [CURRENT_TIMESTAMP]     | [GETDATE]               | [GETUTCDATE]            |
|-------------------------|-------------------------|-------------------------|
| 2019-04-15 06:42:56.010 | 2019-04-15 06:42:56.010 | 2019-04-15 03:42:56.010 |

仅获取日期

SELECT 
    CONVERT(date, SYSDATETIME()) AS [SYSDATETIME], 
    CONVERT(date, SYSDATETIMEOFFSET()) AS [SYSDATETIMEOFFSET], 
    CONVERT(date, SYSUTCDATETIME()) AS [SYSUTCDATETIME], 
    CONVERT(date, CURRENT_TIMESTAMP) AS [CURRENT_TIMESTAMP],
    CONVERT(date, GETDATE()) AS [GETDATE], 
    CONVERT(date, GETUTCDATE()) AS [GETUTCDATE];

| [SYSDATETIME] | [SYSDATETIMEOFFSET] | [SYSUTCDATETIME] | [CURRENT_TIMESTAMP] | [GETDATE]   |[GETUTCDATE]|
|---------------|---------------------|------------------|---------------------|-------------|--------------|
| 2018-11-22    | 2018-11-22          | 2018-11-22       | 2018-11-22          | 2018-11-22  | 2018-11-22   |

仅获取时间

SELECT 
    CONVERT(time, SYSDATETIME()) AS [SYSDATETIME], 
    CONVERT(time, SYSDATETIMEOFFSET()) AS [SYSDATETIMEOFFSET], 
    CONVERT(time, SYSUTCDATETIME()) AS [SYSUTCDATETIME], 
    CONVERT(time, CURRENT_TIMESTAMP) AS [CURRENT_TIMESTAMP],
    CONVERT(time, GETDATE()) AS [GETDATE], 
    CONVERT(time, GETUTCDATE()) AS [GETUTCDATE]

| [SYSDATETIME]    | [SYSDATETIMEOFFSET] | [SYSUTCDATETIME] | [CURRENT_TIMESTAMP] | [GETDATE]        | [GETUTCDATE]     |
|------------------|---------------------|------------------|---------------------|------------------|------------------|
| 07:03:13.4127151 | 07:03:13.4142034    | 04:03:13.4142034 | 07:03:13.4133333    | 07:03:13.4133333 | 04:03:13.4133333 |

Vamos praticar!

在 SQL Server 中使用函数处理数据