Funzioni che restituiscono data e ora di sistema

Funzioni per manipolare i dati in SQL Server

Ana Voicu

Data Engineer

Errori comuni con date e orari

Formati/modelli data-ora incoerenti
Operazioni aritmetiche
Problemi di fusi orari

Fusi orari in SQL Server

Fuso orario locale
Fuso orario UTC (Tempo Coordinato Universale)

Funzioni che restituiscono data e ora del sistema operativo

Alta precisione

SYSDATETIME()
SYSUTCDATETIME()
SYSDATETIMEOFFSET()

Bassa precisione

GETDATE()
GETUTCDATE()
CURRENT_TIMESTAMP

Esempio di funzioni ad alta precisione

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 |

Esempio di funzioni a bassa precisione

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 |

Recuperare solo la data

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   |

Recuperare solo l’ora

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 |

Ayo berlatih!

Funzioni per manipolare i dati in SQL Server