SQL – I tipi data e le conversioni da stringa

13 nov 2014 23:03

_Logo Primo articolo di una serie di note dedicate a SQL nella versione per SQL Server. Inizio con i tipi data disponibili in T-SQL che non si limitano a datetime e date ma includono altre tipologie più standard ed utili. Aggiungo inoltre alcuni esempi di conversione da stringa.

Ecco i tipi data disponibili in SQL Server, come esposti su MSDN:

  • date: il suo formato stringa di default è ISO 8601 (YYYY-MM-DD). L’intervallo supportato va da 0001-01-01 a 9999-12-31 con una precisione di un giorno. Richiede 3 bytes ed è disponibile dalla versione 2008.
  • datetime: richiede 8 bytes perché oltre alla data permette di memorizzare l’orario. L’intervallo va dal 1 gennaio 1753 al 31 dicembre 9999. Precisione al millesimo di secondo (da 0 a 999). Non essendo standard SQL è da considerare deprecato. Dalla versione 2005 è onnipresente.
  • datetime2: presente dalla versione 2008 permette di indicare nella definizione tre parametri: fractional, seconds, precision. Richiede da 6 (precision < 3) a 8 byte a seconda della precisione richiesta (quindi per le frazioni di secondo, che hanno intervallo 0 to 9999999). Il suo formato standard è YYYY-MM-DD hh:mm:ss[.fractional seconds] e l’intervallo dall’anno va da 0001 a 9999. E’ standard ISO 8601.
  • datetimeoffset: definisce una data con orario e quest’ultimo è legato ad un certo fuso orario. Accetta tre parametri nella definizione: fractional seconds precision. Il suo formato standard è YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]. Anche in questo caso l’intervallo va da 0001 a 9999. Con una precisione di 100ns occupa 10 bytes. Soddisfa lo standard ISO 8601. Dalla versione 2008.
  • smalldatetime: in questo caso la data è accompagnata da un orario in cui i secondi sono sempre 0. L’intervallo è limitato dal 1900 al 2079 e occupa 4 bytes. Non è ne ANSI ne ISO 8601. Dalla versione 2005.
  • time: definisce solo l’orario in formato 24 ore (da 0 a 23:59:59.9999999). Richiede almeno 5 bytes e permette di indicare fractional second precision. Dalla versione 2008 ed soddisfa lo standard ISO 8601.

Come suggerito da Microsoft per i nuovi progetti preferire date, datetime2 e datetimeoffset: sono più allineati agli standard SQL e offrono, per l’orario, maggiore precisione. Inoltre molti database utilizzano datetime anche per memorizzare la sola data, forzando l’ora a 0. In questo caso è meglio date, che occupa meno spazio e ha prestazioni maggiori se usato in un indice.

SQL Server accetta sia anni a due cifre sia nel formato lungo: bisogna però fare attenzione al fatto che il limite di conversione è 49. Quindi, per esempio, 49 viene convertito in 2049 mentre 50 in 1950. Ovviamente è il caso di utilizzare il formato a quattro cifre eventualmente aggiungendo ciò che manca nell'espressione.

Per quanto riguarda la conversione è possibile utilizzare sia CAST sia CONVERT. In quest’ultimo caso con il terzo parametro possiamo indicare lo stile dell'espressione che vogliamo convertire. L'elenco degli stili è disponibile su MSDN. In tutti i casi è possibile indicare anche l'orario: è possibile indicare AM//PM come suffisso oppure usare il formato a 24 ore.

Potete trovare i seguenti esempi direttamente SQLFiddle. SQLFiddle è un ottimo strumento soprattutto perché permette di specificare il tipo e la versione del linguaggio da utilizzare (oltre a SQL Server, MySQL, Oracle, PostgreSQL e SQLite).

SELECT 
     CAST( '2014-12-31' AS datetime )                        -- OK
    ,CAST( '20141231' AS datetime )                          -- OK
    ,CAST( '12-31-2014' AS datetime )                        -- OK
    ,CAST( '2014' AS datetime )                              -- 2014-01-01
    ,CAST( NULL AS datetime )                                -- NULL

    ,CAST( '2014-12-31' AS smalldatetime )                   -- OK

    ,CONVERT( datetime, '2014-12-31')                        -- OK
    ,CONVERT( datetime, '2014-12-31', NULL)                  -- NULL

    ,CONVERT( datetime, 'dec 31 2014', 0)                    -- Default
    ,CONVERT( datetime, '12-31-2014', 101)                   -- U.S.
    ,CONVERT( datetime, '2014.12.31', 102)                   -- ANSI
    ,CONVERT( datetime, '31-12-2014', 105)                   -- Italiano
    ,CONVERT( datetime, '31/12/2014', 105)                   -- Italiano
    ,CONVERT( datetime, '20141231', 112)                     -- ISO
    ,CONVERT( datetime, '2014-12-31T18:26:20.000', 126)      -- ISO8601
    ,CONVERT( datetime, '31 dec 2014 13:59:49:120', 112)     -- Europa + millisecondi

Un altro buon esempio, tratto da MSDN:

SELECT 
     CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time' 
    ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date' 
    ,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS 'smalldatetime' 
    ,CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime' 
    ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS 'datetime2'
    ,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS 'datetimeoffset'

Tag: T-SQL, SQL Server, cast