Functies die systeemdatum en -tijd retourneren

Functies voor het bewerken van data in SQL Server

Ana Voicu

Data Engineer

Veelgemaakte fouten met datum en tijd

Inconsistente datum-/tijdnotatie
Rekenkundige bewerkingen
Tijdzoneproblemen

Tijdzones in SQL Server

Lokale tijdzone
UTC-tijdzone (Universal Time Coordinated)

Functies die de datum en tijd van het besturingssysteem retourneren

Hogere precisie

SYSDATETIME()
SYSUTCDATETIME()
SYSDATETIMEOFFSET()

Lagere precisie

GETDATE()
GETUTCDATE()
CURRENT_TIMESTAMP

Voorbeeld: functies met hogere precisie

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 |

Voorbeeld: functies met lagere precisie

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 |

Alleen de datum ophalen

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   |

Alleen de tijd ophalen

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 |

Laten we oefenen!

Functies voor het bewerken van data in SQL Server