DATEADD (Transact-SQL)

  • Article

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Point de terminaison analytique SQL dans Microsoft FabricEntrepôt dans Microsoft Fabric

Cette fonction ajoute un number (entier signé) au datepart d’une date entrée, et retourne une valeur de date/heure modifiée. Par exemple, vous pouvez utiliser cette fonction pour rechercher la date qui correspond à 7 000 minutes à compter de la date du jour : number = 7000, datepart = minute, date = aujourd’hui.

Pour obtenir une vue d’ensemble de tous les types de données et toutes les fonctions de date et d’heure Transact-SQL, consultez Types de données et fonctions de date et d’heure (Transact-SQL).

Conventions de la syntaxe Transact-SQL

Syntaxe

DATEADD (datepart , number , date )

Remarque

Pour afficher la syntaxe Transact-SQL pour SQL Server 2014 (12.x) et versions antérieures, consultez Versions antérieures de la documentation.

Arguments

datepart

Partie de date à laquelle DATEADD ajoute un number de type integer. Ce tableau répertorie tous les arguments datepart valides.

Notes

DATEADD n’accepte pas d’équivalents de variables définis par l’utilisateur pour les arguments datepart.

datepart Abréviations
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw, w
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond ns

number

Expression qui peut être résolue en int ajouté par DATEADD au datepart de date. DATEADD accepte les valeurs des variables définies par l’utilisateur pour number. DATEADD tronque une valeur numérique spécifique ayant une fraction décimale. Il n’arrondit pas la valeurnumber dans cette situation.

date

Expression qui peut être résolue en valeur, parmi les suivantes :

  • date
  • datetime
  • datetimeoffset
  • datetime2
  • smalldatetime
  • time

Pour date, DATEADD accepte une expression de colonne, une expression, un littéral de chaîne ou une variable définie par l’utilisateur. Une valeur de littéral de chaîne doit être résolue en datetime. Pour éviter toute ambiguïté, utilisez des années à quatre chiffres. Pour obtenir des informations sur les années à deux chiffres, consultez Configurer l’option de configuration du serveur two digit year cutoff.

Types de retour

Le type de données de la valeur de retour pour cette méthode est dynamique. Le type de retour dépend de l’argument fourni pour date. Si la valeur de date est un littéral de chaîne de date, DATEADD retourne une valeur datetime. Si un autre type de données d’entrée valide est fourni pour date, DATEADD retourne le même type de données. DATEADD lève une erreur si l’échelle en secondes du littéral de chaîne compte plus de trois décimales (.nnn) ou si le littéral de chaîne contient la partie de décalage du fuseau horaire.

Valeur de retour

Argument datepart

dayofyear, day et weekday renvoient la même valeur.

Chaque datepart et ses abréviations retournent la même valeur.

Si les conditions suivantes sont remplies :

  • datepart est month.
  • Le mois de date a plus de jours que le mois retourné.
  • Le jour date n’existe pas dans le mois retourné.

DATEADD retourne alors le dernier jour du mois retourné. Par exemple, comme le mois de septembre compte 30 jours, les deux instructions suivantes retournent « 2006-09-30 00:00:00.000 » :

SELECT DATEADD(month, 1, '20060830');
SELECT DATEADD(month, 1, '2006-08-31');

Argument number

L’argument number ne peut pas dépasser la plage des int. Dans les instructions suivantes, l’argument pour number dépasse la plage des int de 1. Ces deux instructions retournent le message d’erreur suivant : Msg 8115, Level 16, State 2, Line 1. Arithmetic overflow error converting expression to data type int.

SELECT DATEADD(year,2147483648, '20060731');  
SELECT DATEADD(year,-2147483649, '20060731');

Argument date

DATEADD n’accepte pas d’argument date incrémenté à une valeur située hors de la plage de son type de données. Dans les instructions suivantes, la valeur number ajoutée à la valeur date dépasse la plage du type de données date. DATEADD retourne le message d’erreur suivant : Msg 517, Level 16, State 1, Line 1 Adding a value to a 'datetime' column caused overflow.

SELECT DATEADD(year,2147483647, '20060731');  
SELECT DATEADD(year,-2147483647, '20060731');

Valeurs retournées pour une date smalldatetime et une partie de date seconde ou fractions de seconde

La partie des secondes d’une valeur smalldatetime est toujours 00. Pour une valeur datesmalldatetime, les remarques suivantes s’appliquent :

  • Si datepart est second et que la valeur de number est comprise entre -30 et +29, DATEADD ne change rien.
  • Si datepart est second et que la valeur de number est inférieure à -30 ou supérieure à +29, DATEADD effectue son ajout en commençant à une minute.
  • Si datepart est millisecond et que la valeur de number est comprise entre -30001 et +29998, DATEADD ne change rien.
  • Si datepart est millisecond et que la valeur de number est inférieure à -30001 ou supérieure à +29998, DATEADD effectue son ajout en commençant à une minute.

Notes

Utilisez DATEADD dans les clauses suivantes :

  • GROUP BY
  • HAVING
  • ORDER BY
  • SELECT <list>
  • WHERE

Précision en fractions de seconde

DATEADD n’autorise aucun ajout si datepart est microsecond ou nanosecond pour les types de données datesmalldatetime, date et datetime.

Les millisecondes ont une échelle de 3 (0,123), les microsecondes une échelle de 6 (0,123456) et les nanosecondes une échelle de 9 (0,123456789). Les types de données time, datetime2 et datetimeoffset ont une échelle maximale de 7 (.1234567). Si datepart est nanosecond, number doit être 100 avant que les fractions de seconde de date augmentent. Une valeur number comprise entre 1 et 49 est arrondie à 0, et une valeur comprise entre 50 et 99 est arrondie à 100.

Ces instructions ajoutent une valeur datepart égale à millisecond, microsecond ou nanosecond.

DECLARE @datetime2 datetime2 = '2007-01-01 13:10:10.1111111';  
SELECT '1 millisecond', DATEADD(millisecond,1,@datetime2)  
UNION ALL  
SELECT '2 milliseconds', DATEADD(millisecond,2,@datetime2)  
UNION ALL  
SELECT '1 microsecond', DATEADD(microsecond,1,@datetime2)  
UNION ALL  
SELECT '2 microseconds', DATEADD(microsecond,2,@datetime2)  
UNION ALL  
SELECT '49 nanoseconds', DATEADD(nanosecond,49,@datetime2)  
UNION ALL  
SELECT '50 nanoseconds', DATEADD(nanosecond,50,@datetime2)  
UNION ALL  
SELECT '150 nanoseconds', DATEADD(nanosecond,150,@datetime2);

Voici le jeu de résultats obtenu.

1 millisecond     2007-01-01 13:10:10.1121111  
2 milliseconds    2007-01-01 13:10:10.1131111  
1 microsecond     2007-01-01 13:10:10.1111121  
2 microseconds    2007-01-01 13:10:10.1111131  
49 nanoseconds    2007-01-01 13:10:10.1111111  
50 nanoseconds    2007-01-01 13:10:10.1111112  
150 nanoseconds   2007-01-01 13:10:10.1111113

Décalage de fuseau horaire

DATEADD n’autorise aucun ajout pour le décalage de fuseau horaire.

Exemples

R. Incrémenter une partie de date d'un intervalle de 1

Chacune de ces instructions incrémente datepart d’un intervalle de 1 :

DECLARE @datetime2 datetime2 = '2007-01-01 13:10:10.1111111';  
SELECT 'year', DATEADD(year,1,@datetime2)  
UNION ALL  
SELECT 'quarter',DATEADD(quarter,1,@datetime2)  
UNION ALL  
SELECT 'month',DATEADD(month,1,@datetime2)  
UNION ALL  
SELECT 'dayofyear',DATEADD(dayofyear,1,@datetime2)  
UNION ALL  
SELECT 'day',DATEADD(day,1,@datetime2)  
UNION ALL  
SELECT 'week',DATEADD(week,1,@datetime2)  
UNION ALL  
SELECT 'weekday',DATEADD(weekday,1,@datetime2)  
UNION ALL  
SELECT 'hour',DATEADD(hour,1,@datetime2)  
UNION ALL  
SELECT 'minute',DATEADD(minute,1,@datetime2)  
UNION ALL  
SELECT 'second',DATEADD(second,1,@datetime2)  
UNION ALL  
SELECT 'millisecond',DATEADD(millisecond,1,@datetime2)  
UNION ALL  
SELECT 'microsecond',DATEADD(microsecond,1,@datetime2)  
UNION ALL  
SELECT 'nanosecond',DATEADD(nanosecond,1,@datetime2);

Voici le jeu de résultats obtenu.

Year         2008-01-01 13:10:10.1111111  
quarter      2007-04-01 13:10:10.1111111  
month        2007-02-01 13:10:10.1111111  
dayofyear    2007-01-02 13:10:10.1111111  
day          2007-01-02 13:10:10.1111111  
week         2007-01-08 13:10:10.1111111  
weekday      2007-01-02 13:10:10.1111111  
hour         2007-01-01 14:10:10.1111111  
minute       2007-01-01 13:11:10.1111111  
second       2007-01-01 13:10:11.1111111  
millisecond  2007-01-01 13:10:10.1121111  
microsecond  2007-01-01 13:10:10.1111121  
nanosecond   2007-01-01 13:10:10.1111111

B. Incrémenter plusieurs niveaux d'une partie de date dans une instruction

Chacune de ces instructions incrémente datepart d’une valeur number assez grande pour incrémenter également la valeur datepart immédiatement supérieure de date :

DECLARE @datetime2 datetime2;  
SET @datetime2 = '2007-01-01 01:01:01.1111111';  
--Statement                                 Result     
-------------------------------------------------------------------   
SELECT DATEADD(quarter,4,@datetime2);     --2008-01-01 01:01:01.1111111  
SELECT DATEADD(month,13,@datetime2);      --2008-02-01 01:01:01.1111111  
SELECT DATEADD(dayofyear,365,@datetime2); --2008-01-01 01:01:01.1111111  
SELECT DATEADD(day,365,@datetime2);       --2008-01-01 01:01:01.1111111  
SELECT DATEADD(week,5,@datetime2);        --2007-02-05 01:01:01.1111111  
SELECT DATEADD(weekday,31,@datetime2);    --2007-02-01 01:01:01.1111111  
SELECT DATEADD(hour,23,@datetime2);       --2007-01-02 00:01:01.1111111  
SELECT DATEADD(minute,59,@datetime2);     --2007-01-01 02:00:01.1111111  
SELECT DATEADD(second,59,@datetime2);     --2007-01-01 01:02:00.1111111  
SELECT DATEADD(millisecond,1,@datetime2); --2007-01-01 01:01:01.1121111

C. Utiliser des expressions comme arguments pour les paramètres de date et de numéro

Ces exemples utilisent différents types d’expressions comme arguments pour les paramètres number et date. Ces exemples utilisent la base de données AdventureWorks.

Spécifier une colonne comme date

Cet exemple ajoute 2 (deux) jours à chaque valeur dans la colonne OrderDate pour dériver une nouvelle colonne nommée PromisedShipDate :

SELECT SalesOrderID  
    ,OrderDate   
    ,DATEADD(day,2,OrderDate) AS PromisedShipDate  
FROM Sales.SalesOrderHeader;

Jeu de résultats partiel :

SalesOrderID OrderDate               PromisedShipDate  
------------ ----------------------- -----------------------  
43659        2005-07-01 00:00:00.000 2005-07-03 00:00:00.000  
43660        2005-07-01 00:00:00.000 2005-07-03 00:00:00.000  
43661        2005-07-01 00:00:00.000 2005-07-03 00:00:00.000  
...  
43702        2005-07-02 00:00:00.000 2005-07-04 00:00:00.000  
43703        2005-07-02 00:00:00.000 2005-07-04 00:00:00.000  
43704        2005-07-02 00:00:00.000 2005-07-04 00:00:00.000  
43705        2005-07-02 00:00:00.000 2005-07-04 00:00:00.000  
43706        2005-07-03 00:00:00.000 2005-07-05 00:00:00.000  
...  
43711        2005-07-04 00:00:00.000 2005-07-06 00:00:00.000  
43712        2005-07-04 00:00:00.000 2005-07-06 00:00:00.000  
...  
43740        2005-07-11 00:00:00.000 2005-07-13 00:00:00.000  
43741        2005-07-12 00:00:00.000 2005-07-14 00:00:00.000

Spécifier des variables définies par l'utilisateur comme numéro et date

Cet exemple spécifie des variables définies par l’utilisateur comme arguments pour number et date :

DECLARE @days INT = 365,   
        @datetime DATETIME = '2000-01-01 01:01:01.111'; /* 2000 was a leap year */;  
SELECT DATEADD(day, @days, @datetime);

Voici le jeu de résultats obtenu.

-----------------------  
2000-12-31 01:01:01.110  
  
(1 row(s) affected)

Spécifier la fonction système scalaire comme date

Cet exemple spécifie SYSDATETIME pour date. La valeur exacte retournée varie selon le jour et l’heure de l’exécution de l’instruction :

SELECT DATEADD(month, 1, SYSDATETIME());

Voici le jeu de résultats obtenu.

---------------------------  
2013-02-06 14:29:59.6727944  
  
(1 row(s) affected)

Spécifier des sous-requêtes scalaires et de fonctions scalaires comme numéro et date

Cet exemple utilise des sous-requêtes scalaires, MAX(ModifiedDate), comme arguments pour number et date. (SELECT TOP 1 BusinessEntityID FROM Person.Person) sert d’argument artificiel pour le paramètre number pour illustrer la sélection d’un argument number dans une liste de valeurs.

SELECT DATEADD(month,(SELECT TOP 1 BusinessEntityID FROM Person.Person),  
    (SELECT MAX(ModifiedDate) FROM Person.Person));

Spécifier des expressions numériques et de fonctions système scalaires comme numéro et date

Cet exemple utilise une expression numérique (-(10/2)), des opérateurs unaires (-), un opérateur arithmétique (/) et des fonctions système scalaires (SYSDATETIME) comme arguments pour number et date.

SELECT DATEADD(month,-(10/2), SYSDATETIME());

Spécifier les fonctions de classement comme nombre

Cet exemple utilise une fonction de classement comme argument pour number.

SELECT p.FirstName, p.LastName  
    ,DATEADD(day,ROW_NUMBER() OVER (ORDER BY  
        a.PostalCode),SYSDATETIME()) AS 'Row Number'  
FROM Sales.SalesPerson AS s   
    INNER JOIN Person.Person AS p   
        ON s.BusinessEntityID = p.BusinessEntityID  
    INNER JOIN Person.Address AS a   
        ON a.AddressID = p.BusinessEntityID  
WHERE TerritoryID IS NOT NULL   
    AND SalesYTD <> 0;

Spécifier une fonction d'agrégation de fenêtres comme numéro

Cet exemple utilise une fonction d’agrégation comme argument pour number.

SELECT SalesOrderID, ProductID, OrderQty  
    ,DATEADD(day,SUM(OrderQty)   
        OVER(PARTITION BY SalesOrderID),SYSDATETIME()) AS 'Total'  
FROM Sales.SalesOrderDetail   
WHERE SalesOrderID IN(43659,43664);  
GO

Contenu connexe