Les formats des littéraux de chaîne affectent la présentation des données dans les applications aux utilisateurs, contrairement au format de stockage d'entier sous-jacent dans SQL Server. Toutefois, SQL Server peut interpréter une valeur de date dans un format de littéral de chaîne, entrée par une application ou un utilisateur pour le stockage ou pour une fonction de date, comme des dates différentes. L'interprétation dépend de la combinaison des paramètres de format de littéral de chaîne, de type de données et d'exécution SET DATEFORMAT, SET LANGUAGE et de l'option de langue par défaut.
Certains formats de littéraux de chaîne ne sont pas affectés par ces paramètres. Envisagez l'utilisation d'un format qui ne dépend pas de ces paramètres, sauf si vous êtes sûr que ces paramètres sont adaptés au format. Le format ISO 8601 ne dépend pas de ces paramètres. Il s'agit d'une norme internationale. Transact-SQL, qui utilise des formats de littéraux de chaîne dépendant des paramètres du système, est moins portable.
Pour connaître le format de littéral de chaîne par défaut des clients de bas niveau, consultez la rubrique relative à chaque type de données de date et d'heure. Pour obtenir une vue d'ensemble de tous les types de données et fonctions de date et d'heure Transact-SQL, consultez Fonctions de date et d'heure (Transact-SQL).
Le format de date ydm n'est pas pris en charge pour les types date, datetime2 et datetimeoffset. Une erreur d'exécution sera déclenchée.
Formats des littéraux de chaîne de date et d'heure
Le tableau suivant répertorie les différents formats de chaîne de date et d'heure. Il est recommandé d'utiliser des formats de date et d'heure qui ne dépendent pas de DATEFORMAT et qui sont multilingues. Les formats ISO 8601, '1998-02-23T14:23:05' et '1998-02-23T14:23:05 -08:00' sont les seuls formats qui correspondent à une norme internationale. Ils ne dépendent pas de DATEFORMAT ou de la langue par défaut de la connexion et sont multilingues.
Partie de date-heure | Type de format | Exemple de format | Peut être combiné avec d'autres formats | Dépend de DATEFORMAT | Multilingue |
|---|
Date | Non séparée ISO 8601 | '19980223' | Oui | Non | Oui |
Date | Numérique | '02/23/1998' | Oui | oui | Non (DATEFORMAT) |
Date | Représentation numérique ISO 8601 | '1998-02-23' | Oui | Non | Non |
Date | Alphabétique | '23 février 1998' | Oui | Non | Non (mois ou abréviation du mois) |
Date | Date ODBC | {d '1998-02-23'} | Non | Non | Oui |
Heure | Heure ISO 8601 | '14:23:05' '10:00:00.123456' | Oui | Non | Oui |
Heure | Heure ODBC | {t '14:23:05'} | Non | Non | Oui |
Date-heure | Date-heure ODBC | {ts '1998-02-23 14:23:05'} | Non | Non | Oui |
Date-heure | ISO 8601 | '1998-02-23T14:23:05' '1998-02-23T14:23:05 -08:00' | Non | Non | Oui date, datetime2, datetimeoffset. |
Date-heure | Norme ANSI SQL | '1998-02-23 14:23:05' '1998-02-23 14:23:05 -08:00' | Non | Non (datetime2, datetimeoffset) Oui (datetime) | Oui date, datetime2, datetimeoffset. |
Date-heure | Combinaison de date et d'heure | '19980223 14:23:05' '02/23/1998 2:23:05 PM' '1998-02-23 10:00:00.123' '23 fév 1998 14:23:05' | Non | Oui (partie de date) | Non |
TimeZone | Format TimeZone | '+12:00' '01:00' '-08:00' 'Z' | Oui | Non | Oui |
Les instructions suivantes décrivent les effets des paramètres SET LANGUAGE et SET DATEFORMAT.
DECLARE @Today date = '12/1/2003';
DECLARE @MyTime time = '12:30:22';
DECLARE @MyDatetimeoffset datetimeoffset = '12/1/2003 12:30:22 -5:00';
SET LANGUAGE Italian
-- Returns: Changed language setting to Italiano.
SELECT DATENAME(month, @Today) AS 'Month Name';
-- Returns: dicembre
SELECT CONVERT(VARCHAR(30), @MyDatetimeoffset) AS 'DATETIMEOFFSET';
-- Returns: dic 1 2003 12:30PM -05:00
SELECT DATENAME(hour, @MyTime) As 'Hour Name';
-- Returns: 12
SET LANGUAGE us_english;
-- Returns: Changed language setting to us_english.
SELECT DATENAME(month, @Today) AS 'Month Name';
-- Returns: December
SELECT CONVERT(VARCHAR(30), @MyDatetimeoffset) AS 'DATETIMEOFFSET';
-- Returns: Dec 1 2003 12:30PM -05:00
SELECT DATENAME(hour, @MyTime) As 'Hour Name';
-- Returns: 12
GO
-- Set date format to month, day, year.
SET DATEFORMAT mdy;
GO
DECLARE @datevar date = '12/31/1998';
SELECT @datevar AS DateVar;
-- Returns: 1998-12-31
GO
-- Set date format to year, day, month.
SET DATEFORMAT ydm;
GO
DECLARE @datevar datetimeoffset = '1998/31/12 12:30:22 -05:00';
SELECT @datevar AS DateVar;
-- Returns: Msg 241, Conversion failed when converting
-- date and/or time from character string.
GO
-- Set date format to year, month, day.
SET DATEFORMAT ymd;
GO
DECLARE @datevar date = '12/31/1998';
SELECT @datevar AS DateVar;
-- Returns: 1998-12-31
GO
Format de chaîne non séparée
Vous avez la possibilité de spécifier les données de date comme une chaîne non séparée. Les données de date peuvent être spécifiées en utilisant quatre, six ou huit chiffres, une chaîne vide ou une valeur d'heure sans valeur de date.
Le paramètre de session SET DATEFORMAT ne s'applique pas aux entrées de date entièrement numériques, telles que les entrées numériques sans séparateurs. Les chaînes à six ou huit chiffres sont toujours interprétées comme ymd. Le jour et le mois doivent toujours comporter deux chiffres.
Voici le format valide de chaîne non séparée : [19]960415
Une chaîne de seulement quatre chiffres est interprétée comme l'année. Le mois et la date sont définis sur le 1er janvier. Lorsque vous ne spécifiez que quatre chiffres, vous devez inclure le siècle.
Format ISO 8601
Le format ISO 8601 de date avec heure est le suivant :
YYYY-MM-DDThh:mm:ss[.nnnnnnn][{+|-}hh:mm]
YYYY-MM-DDThh:mm:ss[.nnnnnnn]Z (UTC, temps universel coordonné)
Le T indique le début de la partie heure de la valeur date-heure.
Pour respecter le format ISO 8601, vous devez spécifier chaque élément dans ce format. Cela inclut le T, les deux-points (:), le signe + ou - et les points (.). Les crochets indiquent que les fractions de seconde ou les composants du décalage de fuseau horaire sont facultatifs.
Le composant heure s'exprime au format 24 heures.
Le format ISO 8601 présente un avantage de taille puisqu'il s'agit d'une norme internationale. Les valeurs de date et d'heure spécifiées dans ce format ne sont jamais ambiguës. Ce format n'est pas affecté par les paramètres SET DATEFORMAT, SET LANGUAGE ou ceux de la langue par défaut de la connexion.
Les deux exemples ci-dessous présentent des valeurs de date-heure exprimées au format ISO 8601 :
Formats de date alphabétiques
Vous pouvez spécifier un mois par un nom, par exemple April ou l'abréviation Apr en anglais. Les mois doivent être spécifiés dans le paramètre LANGUAGE de la session, par exemple avril ou avr en français. Les virgules sont facultatives et les majuscules sont ignorées.
Instructions relatives à l'utilisation des formats de date alphabétiques :
Placez la date et l'heure entre des guillemets simples (').
Si vous spécifiez uniquement les deux derniers chiffres de l'année, les valeurs inférieures aux deux derniers chiffres de la valeur de l'option de configuration Année de coupure à deux chiffres appartiennent au même siècle que l'année de troncature. Les valeurs supérieures ou égales à la valeur de cette option appartiennent au siècle qui précède l'année de troncature. Par exemple, si l'option two digit year cutoff a pour valeur 2050 (valeur par défaut), 25 est interprété comme 2025 et 50 est interprété comme 1950. Pour éviter toute ambiguïté, représentez les années à l'aide de quatre chiffres.
Si le jour n'est pas précisé, le premier jour du mois est rajouté.
Le paramètre de session SET DATEFORMAT n'est pas appliqué lorsque vous précisez le mois sous forme alphabétique.
Les formats suivants sont les formats alphabétiques valides pour les données de date de SQL Server : Les caractères placés entre crochets sont facultatifs.
Apr[il] [15][,] 1996
Apr[il] 15[,] [19]96
Apr[il] 1996 [15]
[15] Avr[il][,] 1996
15 Avr[il][,][19]96
15 [19]96 apr[il]
[15] 1996 apr[il]
1996 APR[IL] [15]
1996 [15] APR[IL]
Formats de date numériques
Vous pouvez spécifier les données de date à l'aide d'un mois spécifié sous forme numérique. Par exemple, 5/20/97 correspond au vingtième jour du mois de mai 1997. Lorsque vous utilisez un format de date numérique, spécifiez l'année, le mois et le jour dans une chaîne avec des barres obliques (/), des traits d'union (-) ou des points (.) comme séparateurs. Cette chaîne doit apparaître dans le format suivant :
numéro séparateur numéro séparateur numéro [heure] [heure]
Les formats numériques suivants sont valides (où d=jour, m=mois et y=année) :
[0]4/15/[19]96 -- (mdy)
[0]4-15-[19]96 -- (mdy)
[0]4.15.[19]96 -- (mdy)
[0]4/[19]96/15 -- (maj)
15/[0]4/[19]96 -- (jma)
15/[19]96/[0]4 -- (dym)
[19]96/15/[0]4 -- (ydm)
[19]96/[0]4/15 -- (ymd)
Le paramètre DATEFORMAT de la langue par défaut d'une session est défini par la langue par défaut de la connexion, une instruction SET LANGUAGE ou une instruction SET DATEFORMAT. Lorsque la langue a la valeur us_english du fait de la connexion par défaut ou de l'instruction SET LANGUAGE, l'ordre par défaut de la date est mdy.
Vous pouvez modifier l'ordre de la date à l'aide de l'instruction SET DATEFORMAT. Le paramètre de SET DATEFORMAT détermine l'interprétation des valeurs de date. Si l'ordre ne correspond pas au paramètre, les valeurs ne sont pas interprétées comme des dates (car elles sont hors limites) ou elles sont mal interprétées. Par exemple, 12/10/08 peut être interprété comme 6 dates différentes, selon le paramétrage de DATEFORMAT. Une année à quatre chiffres sera interprétée comme année.
Formats d'heure
SQL Server reconnaît les formats suivants pour les données d'heure. Mettez chaque format entre guillemets simples (').
14:30
14:30[:20:999]
14:30[:20.9]
4am
4 PM
[0]4[:30:20:500]AM
Les instructions suivantes présentent les valeurs retournées des différentes entrées de la fonction CAST.
SELECT CAST('01/01/2000 14:30' AS datetime2)
--Returns: 2000-01-01 14:30:00.0000000
SELECT CAST('01/01/2000 14:30:20:999' AS datetime2)
-- Returns: 2000-01-01 14:30:20.9990000
SELECT CAST('01/01/2000 14:30:20.9' AS datetime2)
-- Returns: 2000-01-01 14:30:20.9000000
SELECT CAST('01/01/2000 4am' AS datetime2)
-- Returns: 2000-01-01 04:00:00.0000000
SELECT CAST('01/01/2000 4 PM' AS datetime2)
-- Returns: 2000-01-01 16:00:00.0000000
SELECT CAST('01/01/2000 04:30:20:500AM' AS datetime2)
-- Returns: 2000-01-01 04:30:20.5000000
SELECT CAST('01/01/2000 04:30:20:500 AM' AS datetime2)
-- Returns: 2000-01-01 04:30:20.5000000
GO
Vous pouvez utiliser un suffixe AM ou PM pour indiquer si la valeur d'heure se situe avant ou après midi. La casse est ignorée pour AM ou PM.
Les heures peuvent être définies au format 12 ou 24 heures. Les valeurs d'heure sont interprétées de la façon suivante :
La valeur d'heure 00 représente l'heure après minuit (AM), que le suffixe AM soit utilisé ou non. Vous ne pouvez pas spécifier PM lorsque la valeur d'heure est égale à 00.
Les valeurs d'heure comprises entre 01 et 11 représentent les heures avant midi si AM ou PM n'est pas spécifié. Elles représentent également les heures avant midi si AM est spécifié et les heures après midi si PM est spécifié.
La valeur 12 pour les heures représente l'heure qui démarre à midi si AM ou PM n'est pas spécifié. Elle représente l'heure qui démarre à minuit si AM est spécifié et l'heure qui démarre à midi si PM est spécifié. Par exemple, 12:01 correspond à 1 minute après midi, et 12:01 PM, alors que 12:01 AM équivaut à 1 +minute après minuit. La spécification de 12:01 AM équivaut à 00:01 ou 00:01 AM.
Les heures situées entre 13 et 23 représentent les heures après midi si AM ou PM n'est pas précisé. Elles représentent également les heures après midi si PM est spécifié. Vous ne pouvez pas spécifier AM lorsque la valeur d'heure est comprise entre 13 et 23.
La valeur 24 pour l'heure n'est pas valide, utilisez 12:00 AM ou 00:00 pour indiquer minuit.
Les millisecondes peuvent être précédées de deux points (:) ou d'un point (.). Précédé de deux points, il s'agit de millièmes de secondes. Précédé d'un point, un chiffre unique représente un dixième de seconde, deux chiffres un centième et trois chiffres un millième. Par exemple, 12:30:20:1 indique vingt secondes et un millième après 12:30 ; 12:30:20.1 indique vingt secondes et un dixième après 12:30.
Format de date et d'heure ODBC
L'API ODBC définit des séquences d'échappement pour représenter les valeurs de date et d'heure, ce qu'ODBC appelle des données timestamp. Ce format d'horodateur ODBC est également pris en charge par la définition de langage OLE DB (DBGUID-SQL) par le fournisseur Microsoft OLE DB pour SQL Server. Les applications qui utilisent les API basées sur ADO, OLE DB et ODBC peuvent utiliser ce format d'horodateur ODBC pour représenter les dates et les heures.
SQL Server traite toujours les données ODBC comme appartenant au type de données datetime.
Les séquences d'échappement d'horodateur ODBC ont le format suivant :
{ literal_type 'constant_value' }
- literal_type
Spécifie le type de séquence d'échappement. Les arguments valides pour literal_type sont les suivants :
d = date uniquement
t = heure uniquement
ts = horodatage (heure + date)
- 'constant_value'
Valeur de la séquence d'échappement. constant_value doit respecter les formats suivants pour chaque literal_type.
literal_type | Format de constant_value |
|---|
d
|
AAAA-MM-JJ
|
t
|
hh:mm:ss[.fff] |
ts
|
AAAA-MM-JJ
hh:mm:ss[.fff] |
Voici des exemples de constantes d'heures et de dates ODBC :
Ne confondez pas le nom du type de données timestamp ODBC et OLE DB avec le nom du type de données timestampTransact-SQL. Le type de données timestamp ODBC et OLE DB enregistre les dates et les heures. Le type de données timestampTransact-SQL est un type de données binaire dont les valeurs ne sont pas liées au temps.