Utilisation des données de date et d'heure
Les sections suivantes de cette rubrique fournissent des informations et des exemples sur l'utilisation des types de données et fonctions 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).
Utilisation d'opérateurs avec les types de données de date et d'heure
Utilisation des formats de date et d'heure
Formats de date et d'heure de littéral de chaîne
Format de chaîne non séparée
Format ISO 8601
Formats de date alphabétiques
Formats de date numériques
Formats d'heure
Format de date et d'heure ODBC
Conversion de la date, de l'heure, de datetime2 et de datetimeoffset
Conversion entre littéraux de chaîne et heure (n), date, datetime2 (n) et datetimeoffset (n)
Conversion entre types de données de date et d'heure
Utilisation de CAST et CONVERT avec l'heure, la date, datetime2 et datetimeoffset
Arguments de style pour les fonctions CONVERT
Limites de date et d'heure de SQL Server
Compatibilité descendante pour les clients de bas niveau
Exemples
Utilisation d'opérateurs avec les types de données de date et d'heure
Les opérateurs relationnels (<, <=, >, >=, <>), les opérateurs de comparaison (=, <, <=, >, >=, <>, !<, !>), ainsi que les opérateurs logiques et les prédicats booléens (IS NULL, IS NOT NULL, IN, BETWEEN, EXISTS, NOT EXISTS et LIKE) sont pris en charge pour tous les types de données de date et d'heure.
Opérateurs arithmétiques de date et d'heure
Utilisation des formats de date et d'heure
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 :
2004-05-23T14:25:10
2004-05-23T14:25:10.1234567+07:00
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-JJhh:mm:ss[.fff]
Voici des exemples de constantes d'heures et de dates ODBC :
{ ts '1998-05-02 01:23:56.123' }
{ d '1990-10-02' }
{ t '13:33:41' }
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.
Conversion de la date, de l'heure, de datetime2 et de datetimeoffset
Il existe deux types de conversions entre différents types de dates : explicite et implicite. Une conversion implicite est une conversion pour laquelle il n'est pas nécessaire de spécifier les fonctions CAST ou CONVERT. Les conversions explicites utilisent les fonctions CAST ou CONVERT.
Conversion entre littéraux de chaîne et heure (n), date, datetime2 (n) et datetimeoffset (n)
Les conversions de littéraux de chaîne en types de date et d'heure sont autorisées si toutes les parties des chaînes sont dans des formats valides. Sinon, une erreur d'exécution est déclenchée.
Les conversions implicites ou explicites qui ne spécifient pas de style à partir de types de date et d'heure en littéraux de chaîne seront au format par défaut de la session active.
Pour la conversion implicite des types de données date, heure, datetime2 et datetimeoffset en chaînes de caractères, le format standard SQL YYYY-MM-DD hh:mi:ss.[nnnnnnn], style CONVERT 12, sera appliqué. Le format de style CONVERT 0, mois jj aaaa hh:miAM (ou PM) est appliqué aux types de données datetime et smalldatetime.
Le tableau suivant présente les règles de conversion entre les types date, time, datetime2 et datetimeoffset, et les littéraux de chaîne.
Littéral de chaîne d'entrée |
date |
time(n) |
datetime2(n) |
datetimeoffset(n) |
|---|---|---|---|---|
ODBC DATE |
Consultez la remarque 1. |
Consultez la remarque 1. |
Consultez la remarque 1. |
Consultez la remarque 1. |
ODBC TIME |
Consultez la remarque 1. |
Consultez la remarque 1. |
Consultez la remarque 1. |
Consultez la remarque 1. |
ODBC DATETIME |
Consultez la remarque 1. |
Consultez la remarque 1. |
Consultez la remarque 1. |
Consultez la remarque 1. |
DATE uniquement |
Simple |
Les valeurs par défaut sont fournies |
La partie TIME a pour valeur par défaut 00:00:00. |
La partie TIME a pour valeur par défaut 00:00:00. TIMEZONE a pour valeur par défaut +00:00. |
TIME uniquement |
Les valeurs par défaut sont fournies |
Simple |
La partie DATE a pour valeur par défaut 1900-1-1. |
La partie DATE a pour valeur par défaut 1900-1-1. TIMEZONE aura pour valeur par défaut +00:00. |
TIMEZONE uniquement |
Les valeurs par défaut sont fournies |
Les valeurs par défaut sont fournies |
Les valeurs par défaut sont fournies |
Les valeurs par défaut sont fournies |
DATE + TIME |
La partie DATE de la chaîne d'entrée est utilisée. |
La partie TIME de la chaîne d'entrée est utilisée. |
Simple |
TIMEZONE a pour valeur par défaut +00:00. |
DATE + TIMEZONE |
Non autorisé |
Non autorisé |
Non autorisé |
Non autorisé |
TIME + TIMEZONE |
Les valeurs par défaut sont fournies |
La partie TIME de la chaîne d'entrée est utilisée. |
La partie DATE a pour valeur par défaut 1900-1-1. L'entrée TIMEZONE est ignorée. |
La partie DATE a pour valeur par défaut 1900-1-1. |
DATE + TIME + TIMEZONE |
La partie DATE du DATETIME local sera utilisée. |
La partie TIME du DATETIME local sera utilisée. |
Le DATETIME local sera utilisé. |
Simple |
Remarques sur la conversion
Les littéraux de chaîne ODBC sont mappés sur le type de données datetime. Toute opération d'affectation de littéraux ODBC DATETIME en des types date, time, datetime2, or datetimeoffset provoquera une conversion implicite entre datetime et ces types, comme défini par les règles de conversion.
La précision en fractions de seconde de datetime est de l'ordre d'un trois-centième de seconde (soit 3,33 millisecondes ou 0,00333 secondes). Les valeurs sont arrondies à des incréments de .000, .003 ou .007 secondes. '08/22/1995 10:15:19:999' est arrondi car '.999' dépasse la précision.
Pour time(3), datetime2(3) ou datetimeoffset(3), la précision en fractions de seconde est de l'ordre d'une milliseconde. Par conséquent, '1995-8-22 10:15:19:999' ne sera pas arrondi.
La partie offset du TIMEZONE d'entrée doit toujours être composée de deux chiffres pour hh et mm. Le signe + ou - est obligatoire.
Conversion entre types de données de date et d'heure
Les tableaux de cette section décrivent comment chacun des types de données de date et d'heure suivants est converti en autres types de données de date et d'heure :
date
time(n)
datetime
smalldatetime
datetimeoffset(n)
datetime2
Type de données date
Le tableau suivant décrit ce qui se produit lorsqu'un type de données date est converti en d'autres types de données de date et d'heure.
Type de données vers lequel effectuer la conversion |
Détails de conversion |
|---|---|
time(n) |
La conversion échoue et le message d'erreur 206 est généré : « Conflit de types d'opérandes : date est incompatible avec time ». |
datetime |
La date est copiée. Le code suivant montre les résultats de la conversion d'une valeur date en valeur datetime. |
smalldatetime |
Lorsque la valeur de date est comprise dans la plage d'un smalldatetime, le composant date est copié et le composant time est défini sur 00:00:00.000. Lorsque la valeur de date est située en dehors de la plage d'une valeur smalldatetime, le message d'erreur 242 est généré : « La conversion d'un type de données date en type de données smalldatetime a créé une valeur hors limites » ; et la valeur smalldatetime est définie sur NULL. Le code suivant montre les résultats de la conversion d'une valeur date en valeur smalldatetime. |
datetimeoffset(n) |
La date est copiée et l'heure est définie sur 00:00.0000000 +00:00. Le code suivant montre les résultats de la conversion d'une valeur date en valeur datetimeoffset(3). |
datetime2(n) |
Le composant date est copié et le composant heure est défini sur 00:00.000000. Le code suivant montre les résultats de la conversion d'une valeur date en valeur datetime2(3). |
Type de données time(n)
Le tableau suivant décrit ce qui se produit lorsqu'un type de données time est converti en d'autres types de données de date et d'heure.
Type de données vers lequel effectuer la conversion |
Détails de conversion |
|---|---|
time(n) |
Les heures, les minutes et les secondes sont copiées. Lorsque la précision de destination est inférieure à la précision source, les fractions de seconde sont tronquées en fonction de la précision de destination. L'exemple suivant montre les résultats de la conversion d'une valeur time(4) en valeur time(3). |
date |
La conversion échoue et le message d'erreur 206 est généré : « Conflit de types d'opérandes : date est incompatible avec time ». |
datetime |
Les valeurs d'heures, de minutes et de secondes sont copiées et le composant date est défini sur « 1900-01-01 » Lorsque la précision de fraction de seconde de la valeur time(n) est supérieure à trois chiffres, le résultat datetime est tronqué. Le code suivant montre les résultats de la conversion d'une valeur time(4) en valeur datetime. |
smalldatetime |
La date est défini sur '1900-01-01' et les valeurs d'heures et de minutes sont copiées. Les secondes et fractions de seconde sont définies sur 0. Le code suivant montre les résultats de la conversion d'une valeur time(4) en valeur smalldatetime. |
datetimeoffset(n) |
La date est défini sur '1900-01-01' et l'heure est copiée. Le décalage de fuseau horaire est défini sur +00:00. Lorsque la précision de fraction de seconde de la valeur time(n) est supérieure à la précision de la valeur datetimeoffset(n), la valeur est tronquée en conséquence. L'exemple suivant montre les résultats de la conversion d'une valeur time(4) en type datetimeoffset(3). |
datetime2(n) |
La date est définie sur '1900-01-01', le composant heure est copié et le décalage de fuseau horaire est défini sur 00:00. Lorsque la précision de fraction de seconde de la valeur datetime2(n) est supérieure à la valeur time(n), la valeur est tronquée en conséquence. L'exemple suivant montre les résultats de la conversion d'une valeur time(4) en valeur datetime2(2). |
Type de données datetime
Le tableau suivant décrit ce qui se produit lorsqu'un type de données datetime est converti en d'autres types de données de date et d'heure.
Type de données vers lequel effectuer la conversion |
Détails de conversion |
|---|---|
date |
Le jour, le mois et l'année sont copiés. Le composant heure est défini sur 00:00:00.000. Le code suivant montre les résultats de la conversion d'une valeur date en valeur datetime. |
time(n) |
Le composant heure est copié et le composant date est défini sur '1900-01-01'. Lorsque la précision de fraction de la valeur time(n) est supérieure à trois chiffres, la valeur est tronquée en conséquence. L'exemple suivant montre les résultats de la conversion d'une valeur time(4) en valeur datetime. |
smalldatetime |
Les heures et les minutes sont copiées. Les secondes et fractions de seconde sont définies sur 0. Le code suivant montre les résultats de la conversion d'une valeur smalldatetime en valeur datetime. |
datetimeoffset(n) |
Les composants date et heure sont copiés. Le fuseau horaire est tronqué. Lorsque la précision de fraction de la valeur datetimeoffset(n) est supérieure à trois chiffres, la valeur est tronquée en conséquence. L'exemple suivant montre les résultats de la conversion d'une valeur datetimeoffset(4) en valeur datetime. |
datetime2(n) |
La date et l'heure sont copiées. Lorsque la précision de fraction de la valeur datetime2(n) est supérieure à trois chiffres, la valeur est tronquée en conséquence. L'exemple suivant montre les résultats de la conversion d'une valeur datetime2(4) en valeur datetime. |
Type de données smalldatetime
Le tableau suivant décrit ce qui se produit lorsqu'un type de données smalldatetime est converti en d'autres types de données de date et d'heure.
Type de données vers lequel effectuer la conversion |
Détails de conversion |
|---|---|
date |
Le jour, le mois et l'année sont copiés. Le code suivant montre les résultats de la conversion d'une valeur smalldatetime en valeur date. |
time(n) |
Les heures, minutes et secondes sont copiées. Les fractions de seconde sont définies sur 0. Le code suivant montre les résultats de la conversion d'une valeur smalldatetime en valeur time(4). |
datetime |
La valeur de smalldatetime est copiée dans la valeur de datetime. Les fractions de seconde sont définies sur 0. Le code suivant montre les résultats de la conversion d'une valeur smalldatetime en valeur datetime. |
datetimeoffset(n) |
La valeur de smalldatetime est copiée dans la valeur de datetimeoffset(n). Les fractions de seconde sont définies sur 0 et le décalage de fuseau horaire est défini sur +00:0. Le code suivant montre les résultats de la conversion d'une valeur smalldatetime en valeur datetimeoffset(4). |
datetime2(n) |
La valeur de smalldatetime est copiée dans la valeur de datetime2(n). Les fractions de seconde sont définies sur 0. Le code suivant montre les résultats de la conversion d'une valeur smalldatetime en valeur datetime2(4). |
Type de données datetimeoffset(n)
Le tableau suivant décrit ce qui se produit lorsqu'un type de données datetimeoffset(n) est converti en d'autres types de données de date et d'heure.
Type de données vers lequel effectuer la conversion |
Détails de conversion |
|---|---|
date |
Le jour, le mois et l'année sont copiés. Le code suivant montre les résultats de la conversion d'une valeur datetimeoffset(4) en valeur date. |
time(n) |
Les heures, les minutes, les secondes et les fractions de seconde sont copiées. La valeur de fuseau horaire est tronquée. Lorsque la précision de la valeur datetimeoffset(n) est supérieure à la précision de la valeur time(n), la valeur est tronquée. Le code suivant montre les résultats de la conversion d'une valeur datetimeoffset(4) en valeur time(3). |
datetime |
Les valeurs de date et heure sont copiées et le fuseau horaire est tronqué. Lorsque la précision de fraction de la valeur datetimeoffset(n) est supérieure à trois chiffres, la valeur est tronquée en conséquence. Le code suivant montre les résultats de la conversion d'une valeur datetimeoffset(4) en valeur datetime. |
smalldatetime |
La date, les heures et les minutes sont copiées. Les secondes sont définies sur 0. Le code suivant montre les résultats de la conversion d'une valeur datetimeoffset(3) en valeur smalldatetime. |
datetime2(n) |
Les date et heure sont copiées dans la valeur datetime2 et le fuseau horaire est tronqué. Lorsque la précision de la valeur datetime2(n) est supérieure à la précision de la valeur datetimeoffset(n), la fraction de seconde est tronquée en conséquence. Le code suivant montre les résultats de la conversion d'une valeur datetimeoffset(4) en valeur datetime2(3). |
Type de données datetime2
Le tableau suivant décrit ce qui se produit lorsqu'un type de données datetime2 est converti en d'autres types de données de date et d'heure.
Type de données vers lequel effectuer la conversion |
Détails de conversion |
|---|---|
date |
Le jour, le mois et l'année sont copiés. Le code suivant montre les résultats de la conversion d'une valeur datetime2(4) en valeur date. |
time(n) |
Les heures, les minutes, les secondes et les fractions de seconde sont copiées. Le code suivant montre les résultats de la conversion d'une valeur datetime2(4) en valeur time(3). |
datetime |
Les valeurs de date et heure sont copiées. Lorsque la précision de fraction de la valeur datetimeoffset(n) est supérieure à trois chiffres, la valeur est tronquée en conséquence. Le code suivant montre les résultats de la conversion d'une valeur datetime2 en valeur datetime. |
smalldatetime |
La date, les heures et les minutes sont copiées. Les secondes sont définies sur 0. Le code suivant montre les résultats de la conversion d'une valeur datetime2 en valeur smalldatetime. |
datetimeoffset(n) |
La valeur de datetime2(n) est copiée dans la valeur de datetimeoffset(n). Le décalage de fuseau horaire est défini sur +00:0. Lorsque la précision de la valeur datetime2(n) est supérieure à la précision de la valeur datetimeoffset(n), la valeur est tronquée en conséquence. Le code suivant montre les résultats de la conversion d'une valeur datetime2(5) en valeur datetimeoffset(3). |
Utilisation de CAST et CONVERT avec heure, date, datetime2 et datetimeoffset
Cette section contient des informations sur la conversion entre les types de données de date et d'heure.
Conversion en datetimeoffset
- Chaque fois qu'une valeur datetimeoffset avec fuseau horaire est implicitement dérivée d'une valeur sans fuseau horaire (par exemple, dans le cas d'une simple opération d'affectation), la valeur sans fuseau horaire est traitée comme une valeur locale et le déplacement du fuseau horaire par défaut (00:00) actuel est soustrait de cette valeur pour obtenir l'UTC.
- Le fuseau horaire UTC (00:00) est toujours ajouté lorsque vous convertissez des types de données sans fuseau horaire en datetimeoffset pour les conversions suivantes :
date en datetimeoffset
time en datetimeoffset
datetime2 en datetimeoffset
datetime ou smalldatetime en datetimeoffset
Littéraux de chaîne de date, time ou datetime valides sans fuseau horaire en datetimeoffset
Conversion de datetimeoffset
Lorsque vous convertissez datetimeoffset vers les types sans fuseau horaire suivants, le style 0 (style par défaut) indique toujours que la valeur retournée date, time, datetime2, datetime ou smalltime est au format local de l'offset du fuseau horaire préservé. Le style 1 représente toujours le format UTC.
Chaque fois qu'une valeur de date ou d'heure sans fuseau horaire est implicitement dérivée de l'une des conversions suivantes, la valeur datetimeoffset est traitée comme UTC. Le déplacement du fuseau horaire préservé est ajouté à la valeur pour obtenir l'heure locale. Le résultat est à l'heure locale, sans décalage de fuseau horaire.
datetimeoffset en date
datetimeoffset en time
datetimeoffset en datetime2
datetimeoffset en datetime ou smalldatetime
Il n'est pas possible d'utiliser les styles 0 et 1 pour la conversion de datetimeoffset en chaîne. À la place, convertissez d'abord datetimeoffset en datetime2 ou datetime, puis en varchar ou char.
Si un style CONVERT existant inclut la partie heure et s'il s'agit d'une conversion de datetimeoffset en chaîne, le décalage de fuseau horaire (à l'exception du style 127) est inclus. Si vous ne souhaitez pas de décalage de fuseau horaire, vous avez la possibilité d'utiliser d'abord CAST en datetime2, puis en chaîne.
Les styles de date et d'heure existants s'appliqueront à n'importe quelle conversion de datetimeoffset en chaîne et le décalage de fuseau horaire sera conservé.
La partie décalage de fuseau horaire de la chaîne d'entrée doit toujours être composée de deux chiffres pour hh et mm. Le signe + ou – est obligatoire.
Informations supprimées lors de la conversion
Lorsque vous convertissez datetime2 ou datetimeoffset en date, aucun arrondi n'est effectué et la partie date est explicitement extraite. Pour datetimeoffset, l'extraction est effectuée sur la date et l'heure locales, mais pas sur la valeur UTC.
Pour toute conversion implicite de datetimeoffset to date, time, datetime2, datetime ou smalldatetime, la conversion est basée sur la valeur de la date et de l'heure locales (pour le décalage de fuseau horaire préservé). Par exemple, lorsque la valeur datetimeoffset(3) 2006-10-21 12:20:20.999 -8:00 est convertie en time(3), le résultat est 12:20:20.999 et non 20:20:20.999(UTC).
Troncation dans la conversion
- Les conversions de valeurs d'heure de plus grande précision en valeurs de plus faible précision sont autorisées. Les valeurs de plus grande précision seront tronquées en fonction du type de précision inférieure.
Conversion de fractions de seconde
Si un style inclut le format d'heure hh:mm:ss.mmm, le format devient hh:mm:ss.[nnnnnnn] pour time(n), datetime2(n) et datetimeoffset(n). Le nombre de chiffres dépend de la spécification de type. Si vous souhaitez une précision de l'ordre de la milliseconde uniquement, convertissez d'abord en datetime2(3), puis en chaîne.
Pour les styles 9, 109, 13, 113, 21, 121, 130 et 131, le séparateur deux points (:) précédant les fractions de seconde n'est pas pris en charge pour les types time, datetime2 et datetimeoffset pour la conversion de chaîne. Un format de chaîne de sortie avec l'un de ces styles sera transformé en point (.).
Arguments de style pour les fonctions CONVERT
Le tableau suivant répertorie des exemples de valeurs date, time, datetime2 et datetimeoffset pour les arguments de style de la fonction CONVERT. Pour plus d'informations sur le style, consultez la section Arguments de CAST et CONVERT (Transact-SQL).
Style |
Associated standard |
Input/Output (3) format |
date |
time(n) |
datetime2(n) |
datetimeoffset(n) |
|---|---|---|---|---|---|---|
0 ou 100 (1,2) |
Par défaut |
mois jj aaaa hh:miAM (ou PM) |
Jan 1 2001 |
12:20PM |
Jan 1 2001 12:20PM |
Jan 1 2001 12:20PM -08:00 |
101 |
É-U |
mm/jj/aaaa |
01/01/2001 |
- |
01/01/2001 |
01/01/2001 |
102 |
ANSI |
aa.mm.jj |
2001.01.01 |
- |
2001.01.01 |
2001.01.01 |
103 |
Anglais/français |
jj/mm/aaaa |
01/01/2001 |
- |
01/01/2001 |
01/01/2001 |
104 |
Allemand |
jj.mm.aaaa |
01.01.2001 |
- |
01.01.2001 |
01.01.2001 |
105 |
Italien |
jj-mm-aaaa |
01-01-2001 |
- |
01-01-2001 |
01-01-2001 |
106(1) |
- |
jj mois aaaa |
01 jan 2001 |
- |
01 jan 2001 |
01 jan 2001 |
107(1) |
- |
Mois jj, aa |
Jan 01, 2001 |
- |
Jan 01, 2001 |
Jan 01, 2001 |
108 |
- |
hh:mi:ss |
- |
12:20:20 |
12:20:20 |
12:20:20 |
9 ou 109 (1,2) |
Valeur par défaut + millisecondes |
mois jj aaaa hh:mi:ss:mmmAM (ou PM) |
Jan 1 2001 |
12:20:20.1234567 AM |
Jan 1 2001 12:20:20.1234567PM |
Jan 1 2001 12:20:20:1230000PM -08:00 |
110 |
États-Unis |
mm-jj-aaaa |
01-01-2001 |
- |
01-01-2001 |
01-01-2001 |
111 |
Japon |
aaaa/mm/jj |
2001/01/01 |
- |
2001/01/01 |
2001/01/01 |
112 |
ISO |
aaaammjj |
20010101 |
- |
20010101 |
20010101 |
13 ou 113 (1,2) |
Valeur par défaut Europe + millisecondes |
jj mois aaaa hh:mi:ss:mmm(24h) |
01 jan 2001 |
12:20:20.1234567 |
01 jan 2001 12:20:20.1234567 |
01 jan 2001 12:20:20:1230000 -08:00 |
114 |
- |
hh:mi:ss:mmm(24h) |
- |
12:20:20.1234567 |
12:20:20.1234567 |
12:20:20:1230000 -08:00 |
20 ou 120 (2) |
ODBC canonique |
aaaa-mm-jj hh:mi:ss(24h) |
2001-01-01 |
12:20:20 |
2001-01-01 12:20:20 |
2001-01-01 12:20:20 -08:00 |
21 ou 121 (2) |
ODBC canonique (avec millisecondes) |
aaaa-mm-jj hh:mi:ss.mmm(24h) |
2001-01-01 |
12:20:20.1234567 |
2001-01-01 12:20:20.1234567 |
2001-01-01 12:20:20.1230000 -08:00 |
126 (4) |
ISO8601 |
aaaa-mm-jjThh:mi:ss.mmm (sans espace) |
2001-01-01 |
12:20:20.1234567 |
2001-01-01T 12:20:20.1234567 |
2001-01-01T 12:20:20.1234567 |
127(6, 7) |
ISO8601 avec fuseau horaire Z. |
aaaa-mm-jjThh:mi:ss.mmmZ (sans espaces) |
2001-01-01 |
12:20:20.1234567Z |
2001-01-01T 12:20:20.1234567Z |
2001-01-01T20:20:20.1230000Z |
130 (1,2) |
Hijri (5) |
jj mois aaaa hh:mi:ss:mmmAM |
01 jan 2001 |
12:20:20.1230000PM |
01 jan 2001 12:20:20.1230000PM |
1 jan 2001 12:20:20:1230000PM -08:00 |
131 (2) |
Hijri (5) |
jj/mm/aaaa hh:mi:ss:mmmAM |
01/01/2001 |
12:20:20.1230000PM |
01/01/2001 12:20:20.1230000PM |
01/01/2001 12:20:20.1230000PM -08:00 |
1 Ces valeurs de style retournent des résultats non déterministes. Inclut tous les styles (aam c’est-à-dire sans siècle) et un sous-ensemble de styles (aaaam c'est-à-dire avec siècle).
2 Valeurs par défaut (style0 ou 100, 9 ou 109, 13 ou 113, 20 ou 120, ou encore 21 ou 121) renvoient toujours le siècle (aaaa).
3 Entrée lors de la conversion en données de type datetime ; ou encore, sortie lors de la conversion en données de type caractère.
4 Conçue pour le langage XML. Pour la conversion de données datetime ou smalldatetime en données de type caractère, le format de sortie étant celui décrit dans le tableau précédent.
5 Hijri est un système calendaire possédant de nombreuses variations. SQL Server utilise l'algorithme Kuwaiti.
6 Prise en charge uniquement lors de la conversion de données de type caractère en datetime ou smalldatetime. Lorsque des données de type caractère qui représentent des composants de date ou d'heure uniquement sont converties en types de données datetime ou smalldatetime, la valeur 00:00:00.000 est affectée au composant d'heure non spécifié et la valeur 1900-01-01 est affectée au composant de date non spécifié.
7 L'indicateur de fuseau horaire facultatif Z facilite le mappage des valeurs datetime XML qui possèdent des informations de fuseau horaire avec les valeurs SQL Server datetime dépourvues d'informations de fuseau horaire. Z est l'indicateur du fuseau horaire UTC-0.
Limites de date et d'heure de SQL Server
Dans la liste suivante, la date et l'heure font référence à tous les types de données de date et d'heure qui incluent une partie date ou heure.
Pas de prise en charge de l'heure d'été côté serveur pour datetimeoffset.
Pas de prise en charge du calendrier julien pour la date.
Pas de prise en charge de l'heure pour la représentation de minuit par « 24 » heures.
Pas de prise en charge du saut de seconde au-delà de « 59 ».
Pas de prise en charge de la précision à la nanoseconde ou plus pour les fractions de seconde.
Pas de prise en charge des fuseaux horaires pour l'heure.
Pas de prise en charge de l'opération INTERVAL de la norme SQL.
Compatibilité descendante pour les clients de bas niveau
Certains clients de bas niveau ne prennent pas en charge les types de données time, date, datetime2 et datetimeoffset ajoutés dans SQL Server 2008. Le tableau suivant présente le type de mappage entre une instance de haut niveau de SQL Server 2008 et des clients de bas niveau.
Type de données de SQL Server 2008 |
Format de littéral de chaîne par défaut passé au client de bas niveau |
ODBC de bas niveau |
OLEDB de bas niveau |
JDBC de bas niveau |
SQLCLIENT de bas niveau |
|---|---|---|---|---|---|
time |
hh:mm:ss[.nnnnnnn] |
SQL_WVARCHAR ou SQL_VARCHAR |
DBTYPE_WSTR ou DBTYPE_STR |
Java.sql.String |
String ou SqString |
date |
AAAA-MM-JJ |
SQL_WVARCHAR ou SQL_VARCHAR |
DBTYPE_WSTR ou DBTYPE_STR |
Java.sql.String |
String ou SqString |
datetime2 |
YYYY-MM-JJ hh:mm:ss[.nnnnnnn] |
SQL_WVARCHAR ou SQL_VARCHAR |
DBTYPE_WSTR ou DBTYPE_STR |
Java.sql.String |
String ou SqString |
datetimeoffset |
YYYY-MM-JJ hh:mm:ss[.nnnnnnn] [+|-]hh:mm |
SQL_WVARCHAR ou SQL_VARCHAR |
DBTYPE_WSTR ou DBTYPE_STR |
Java.sql.String |
String ou SqString |
Exemples
A. Comparaison des types de données de date et d'heure
L'exemple suivant compare les résultats de la conversion d'un littéral de chaîne en chaque type de données de date et d'heure. Une tentative de CAST pour un littéral de chaîne dont la précision en fractions de seconde excède celle autorisée pour smalldatetime ou datetime provoque une erreur.
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';
Type de données |
Sortie |
|---|---|
time |
12:35:29.1234567 |
date |
2007-05-08 |
smalldatetime |
2007-05-08 12:35:00 |
datetime |
2007-05-08 12:35:29.123 |
datetime2(7) |
2007-05-08 12:35:29.1234567 |
datetimeoffset(7) |
2007-05-08 12:35:29.1234567 +12:15 |
B. Obtention des date et heure système actuelles
L'exemple suivant montre comment utiliser les fonctions système SQL Server qui retournent les date et heure actuelles.
SELECT SYSDATETIME() AS 'SYSDATETIME'
--Results
--SYSDATETIME
--2007-10-22 14:10:41.7984554
--(1 row(s) affected)
SELECT SYSDATETIMEOFFSET() AS 'SYSDATETIMEOFFSET'
--Results
--SYSDATETIMEOFFSET
--2007-10-22 14:11:34.2607172 -0
--(1 row(s) affected)
SELECT SYSUTCDATETIME() AS 'SYSUTCDATETIME'
--Results
--SYSUTCDATETIME
--2007-10-22 21:12:11.7069470
--(1 row(s) affected)
SELECT CURRENT_TIMESTAMP AS 'CURRENT_TIMESTAMP'
--Results
--CURRENT_TIMESTAMP
-------------------------
--2007-10-22 14:12:33.320
--(1 row(s) affected)
SELECT GETDATE() AS 'GETDATE'
--Results
--GETDATE
--2007-10-22 14:13:57.943
--(1 row(s) affected)
SELECT GETUTCDATE() AS 'GETUTCDATE'
--Results
--GETUTCDATE
--2007-10-22 21:14:35.657
--(1 row(s) affected)
C. Recherche de toutes les valeurs datetime2 d'un jour
- L'exemple suivant indique comment rechercher toutes les valeurs de date et heure dans un jour.
-- Create a table that contains with the following dates:
-- The last date-time in 2005-04-06, '2005-04-06 23:59:59.9999999'
-- The first date-time in 2005-04-07, '2005-04-07 00:00:00.0000000'
-- The last date-time in 2005-04-07, '2005-04-07 23:59:59.9999999'
-- The first date-time in 2005-04-08, '2005-04-08 00:00:00.0000000'
CREATE TABLE #Search
(
MyDate datetime2
);
INSERT INTO #Search(MyDate)VALUES('2005-04-06 23:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-07 00:00:00.0000000');
INSERT INTO #Search(MyDate)VALUES('2005-04-07 23:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-08 00:00:00.0000000');
-- The following four SELECT statements show different ways to find
-- only the two rows that contain 2005-04-07 dates.
--Use CONVERT.
SELECT MyDate
FROM #Search
WHERE CONVERT(date,MyDate) = '2005-04-07';
--Use >= and <=.
SELECT MyDate
FROM #Search
WHERE MyDate >= '2005-04-07 00:00:00.0000000'
AND MyDate <= '2005-04-07 23:59:59.9999999';
--Use > and <.
SELECT MyDate
FROM #Search
WHERE MyDate > '2005-04-06 23:59:59.9999999'
AND MyDate < '2005-04-08 00:00:00.0000000';
--Use BETWEEN AND.
SELECT MyDate
FROM #Search
WHERE MyDate BETWEEN '2005-04-07 00:00:00.0000000'
AND '2005-04-07 23:59:59.9999999';
DROP TABLE #Search
GO
D. Recherche d'une période dans un jour
Les exemples suivants indiquent comment rechercher des dates qui ont des valeurs d'heure afin de trouver une plage horaire.
-- Create a table called Search and insert
-- five different time values for the same
-- date.
CREATE TABLE #Search
(
MyDate datetime2
);
INSERT INTO #Search(MyDate)VALUES('2005-04-06 08:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-06 09:00:00.0000000');
INSERT INTO #Search(MyDate)VALUES('2005-04-06 09:00:00.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-06 16:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-06 17:00:00.0000000');
-- The following SELECT statements show different ways
-- to search for dates that have time values to find a
-- time range.
--Using CONVERT with time (0) to ignore fractional seconds
SELECT MyDate
FROM #Search
WHERE CONVERT(time(0),MyDate) = '09:00:00';
--Returns two rows (ignores fractional seconds):
--2005-04-06 08:59:59.9999999
--2005-04-06 09:00:00.0000000
--Using CONVERT with time (7) to include fractional seconds
SELECT MyDate
FROM #Search
WHERE CONVERT(time(7),MyDate) = '09:00:00';
--Returns one row (matches fractional seconds):
--2005-04-06 09:00:00.0000000
--Each of the SELECT statements below use CONVERT
--to find all times in an eight-hour period.
--Use CONVERT with >= and <=.
SELECT MyDate
FROM #Search
WHERE CONVERT(time(7),MyDate) >= '09:00:00.0000000'
AND CONVERT(time(7),MyDate) <= '16:59:59.9999999'
--Use CONVERT with > and <.
SELECT MyDate
FROM #Search
WHERE CONVERT(time(7),MyDate) > '08:59:59.9999999'
AND CONVERT(time(7),MyDate) < '17:00:00.0000000';
--Use CONVERT with BETWEEN AND
SELECT MyDate
FROM #Search
WHERE CONVERT(time(7),MyDate) BETWEEN '09:00:00.0000000'
AND '16:59:59.9999999';
DROP TABLE #Search
GO
E. Utilisation de DATEPART et DATEADD pour rechercher le premier et le dernier jour d'une partie de date
L'exemple suivant indique comment retourner le premier ou dernier jour d'un datepart.
-- When several months, quarters, or years are added to the base
-- year,1900-01-01, the returned day is the first day of a month.
-- To calculate the last day of the current month, you need to
--
-- 1. Find the difference in months between today and the base
-- year (0 ='1900-01-01'). This is accomplished by
--
-- DATEDIFF(month, 0, SYSDATETIME())
-- 2. Add that number of months plus 1 to the base year (0)
-- to obtain the first day of the next month.
-- DATEADD(month, DATEDIFF(month, 0, SYSDATETIME()) + 1, 0)
-- 3. Subtract 1 day.
--
--Find the first day of the current month.
SELECT DATEADD(month, DATEDIFF(month, 0, SYSDATETIME()), 0);
--Find the last day of the current month.
SELECT DATEADD(month, DATEDIFF(month, 0, SYSDATETIME()) + 1, 0) - 1;
-- Find the first day of the current quarter.
SELECT DATEADD(quarter, DATEDIFF(quarter, 0, SYSDATETIME()), 0);
-- Find the last day of the current quarter.
SELECT DATEADD(quarter, DATEDIFF(quarter, -1, SYSDATETIME()), -1);
-- Find the first day of the current year.
SELECT DATEADD(year, DATEDIFF(year, 0, SYSDATETIME()), 0);
-- Find the last day of the current year.
SELECT DATEADD(year, DATEDIFF(year, -1, SYSDATETIME()), -1);
F. Utilisation des arguments datepart définis par l'utilisateur pour DATEADD, DATEDIFF, DATENAME et DATEPART
L'exemple suivant crée une fonction scalaire définie par l'utilisateur qui ajoute une constante à toute partie d'une valeur datetime2.
USE tempdb
GO
CREATE FUNCTION UserDateadd
(
@datepart nvarchar(11)
,@number int
,@date datetime2
)
RETURNS datetime2
AS
BEGIN
DECLARE @Return datetime2
SELECT @Return = CASE @datepart
WHEN 'year' THEN DATEADD(year,@number,@date)
WHEN 'yy' THEN DATEADD(year,@number,@date)
WHEN 'yyyy' THEN DATEADD(year,@number,@date)
WHEN 'quarter' THEN DATEADD(quarter,@number,@date)
WHEN 'qq' THEN DATEADD(quarter,@number,@date)
WHEN 'month' THEN DATEADD(month,@number,@date)
WHEN 'mm' THEN DATEADD(month,@number,@date)
WHEN 'm' THEN DATEADD(month,@number,@date)
WHEN 'dayofyear' THEN DATEADD(dayofyear,@number,@date)
WHEN 'dy' THEN DATEADD(dayofyear,@number,@date)
WHEN 'y' THEN DATEADD(dayofyear,@number,@date)
WHEN 'day' THEN DATEADD(day,@number,@date)
WHEN 'dd' THEN DATEADD(day,@number,@date)
WHEN 'd' THEN DATEADD(day,@number,@date)
WHEN 'week' THEN DATEADD(week,@number,@date)
WHEN 'wk' THEN DATEADD(week,@number,@date)
WHEN 'ww' THEN DATEADD(week,@number,@date)
WHEN 'weekday' THEN DATEADD(weekday,@number,@date)
WHEN 'wk' THEN DATEADD(weekday,@number,@date)
WHEN 'w' THEN DATEADD(weekday,@number,@date)
WHEN 'hour' THEN DATEADD(hour,@number,@date)
WHEN 'hh' THEN DATEADD(hour,@number,@date)
WHEN 'minute' THEN DATEADD(minute,@number,@date)
WHEN 'mi' THEN DATEADD(minute,@number,@date)
WHEN 'n' THEN DATEADD(minute,@number,@date)
WHEN 'second' THEN DATEADD(second,@number,@date)
WHEN 'ss' THEN DATEADD(second,@number,@date)
WHEN 's' THEN DATEADD(second,@number,@date)
WHEN 'millisecond' THEN DATEADD(millisecond,@number,@date)
WHEN 'ms' THEN DATEADD(millisecond,@number,@date)
WHEN 'microsecond' THEN DATEADD(microsecond,@number,@date)
WHEN 'mcs' THEN DATEADD(microsecond,@number,@date)
WHEN 'nanosecond' THEN DATEADD(nanosecond,@number,@date)
WHEN 'ns' THEN DATEADD(nanosecond,@number,@date)
END
return @Return
END
GO
SELECT GetDate()
SELECT dbo.UserDateadd('year', 1, GetDate())
GO
G. Utilisation de DATEPART pour regrouper des parties de date
L'exemple suivant utilise l'exemple de base de données AdventureWorks2008R2. Le DATEPART est utilisé pour regrouper le total des ventes par jour ouvrable, mois, année et année/mois/jour ouvrable.
USE AdventureWorks2008R2;
GO
SELECT SUM(TotalDue) AS 'Total Sales'
,DATEPART(year,OrderDate)AS 'By Year'
,DATEPART(month,OrderDate) AS 'By Month'
,DATEPART(weekday,OrderDate) AS 'By Weekday'
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) IN('2007','2008')
GROUP BY GROUPING SETS
(
(DATEPART(year,OrderDate))
,(DATEPART(month,OrderDate))
,(DATEPART(weekday,OrderDate))
,(
DATEPART(year,OrderDate)
,DATEPART(month,OrderDate)
,DATEPART(weekday,OrderDate))
)
ORDER BY DATEPART(year,OrderDate)
,DATEPART(month,OrderDate)
,DATEPART(weekday,OrderDate);
Voir aussi