DATEADD: add days to a date : DATEADD « Date Timezone « SQL Server / T-SQL

Home
SQL Server / T-SQL
1.Aggregate Functions
2.Analytical Functions
3.Constraints
4.Cursor
5.Data Set
6.Data Type
7.Database
8.Date Timezone
9.Index
10.Insert Delete Update
11.Math Functions
12.Select Query
13.Sequence
14.Store Procedure Function
15.String Functions
16.Subquery
17.System
18.Table
19.Table Joins
20.Transact SQL
21.Transaction
22.Trigger
23.View
24.XML
SQL Server / T-SQL » Date Timezone » DATEADD 
DATEADD: add days to a date


1create table employee(
2>     ID          int,
3>     name        nvarchar (10),
4>     salary      int,
5>     start_date  datetime,
6>     city        nvarchar (10),
7>     region      char (1))
8> GO
1>
2insert into employee (ID, name,    salary, start_date, city,       region)
3>               values (1,  'Jason', 40420,  '02/01/94', 'New York', 'W')
4> GO

(rows affected)
1insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (2,  'Robert',14420,  '01/02/95', 'Vancouver','N')
3> GO

(rows affected)
1insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (3,  'Celia', 24020,  '12/03/96', 'Toronto',  'W')
3> GO

(rows affected)
1insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (4,  'Linda', 40620,  '11/04/97', 'New York', 'N')
3> GO

(rows affected)
1insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (5,  'David', 80026,  '10/05/98', 'Vancouver','W')
3> GO

(rows affected)
1insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (6,  'James', 70060,  '09/06/99', 'Toronto',  'N')
3> GO

(rows affected)
1insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (7,  'Alison',90620,  '08/07/00', 'New York', 'W')
3> GO

(rows affected)
1insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (8,  'Chris', 26020,  '07/08/01', 'Vancouver','N')
3> GO

(rows affected)
1insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (9,  'Mary',  60020,  '06/09/02', 'Toronto',  'W')
3> GO

(rows affected)
1>
2select from employee
3> GO
ID          name       salary      start_date              city       region
----------- ---------- ----------- ----------------------- ---------- ------
          Jason            40420 1994-02-01 00:00:00.000 New York   W
          Robert           14420 1995-01-02 00:00:00.000 Vancouver  N
          Celia            24020 1996-12-03 00:00:00.000 Toronto    W
          Linda            40620 1997-11-04 00:00:00.000 New York   N
          David            80026 1998-10-05 00:00:00.000 Vancouver  W
          James            70060 1999-09-06 00:00:00.000 Toronto    N
          Alison           90620 2000-08-07 00:00:00.000 New York   W
          Chris            26020 2001-07-08 00:00:00.000 Vancouver  N
          Mary             60020 2002-06-09 00:00:00.000 Toronto    W

(rows affected)
1>
2INSERT INTO Employee (ID, Start_Date)
3>             VALUES   (99, DATEADD(day,-1,GETDATE()))
4>
5select from Employee
6> GO

(rows affected)
ID          name       salary      start_date              city       region
----------- ---------- ----------- ----------------------- ---------- ------
          Jason            40420 1994-02-01 00:00:00.000 New York   W
          Robert           14420 1995-01-02 00:00:00.000 Vancouver  N
          Celia            24020 1996-12-03 00:00:00.000 Toronto    W
          Linda            40620 1997-11-04 00:00:00.000 New York   N
          David            80026 1998-10-05 00:00:00.000 Vancouver  W
          James            70060 1999-09-06 00:00:00.000 Toronto    N
          Alison           90620 2000-08-07 00:00:00.000 New York   W
          Chris            26020 2001-07-08 00:00:00.000 Vancouver  N
          Mary             60020 2002-06-09 00:00:00.000 Toronto    W
         99 NULL              NULL 2006-10-08 15:42:09.670 NULL       NULL

(10 rows affected)
1>
2> drop table employee
3> GO
1>
           
       
Related examples in the same category
1.DATEADD: add or subtract a number of days, months, or years from a specific date
2.SELECT DATEADD(yy,12,"24 March 1964"): Add 12 years to 24th March 1964
3.SELECT DATEADD(m,12,"24 March 1964"): Add 12 months to 24th March 1964
4.SELECT DATEADD(wk,12,"24 March 1964"): Add 12 weeks to 24th March 1964
5.SELECT DATEADD(hh,12,"24 March 1964"): Add 12 hours to 24th March 1964
6.Add 12 minutes to 24th March 1964
7.DATEADD(Day, 90, '4-29-1988'): adds a specific number of date unit intervals to a date/time value
8.DATEADD(year, 18, '4-29-1988 10:30 AM'): 18 years later
9.DATEADD(yy, -18, '4-29-1988 10:30 AM'): 18 years before
10.DATEADD(second, 9000, '4-29-1988 10:30 AM'): 9,000 seconds after
11.DATEADD(mi, -9000000, '4-29-1988 10:30 AM'): 9,000,000 milliseconds before
12.SELECT DATEADD(dayofyear, 18, '4-29-1988 10:30 AM')
13.SELECT DATEADD(quarter, 18, '4-29-1988 10:30 AM')
14.Combine the CONVERT()and the DATEADD() functions to format a return date value nine months before September 8, 1989
15.SELECT DATEADD(mi, -30, '2005-09-01 23:30:00.000')
16.select DATEADD(month, -1, '2002-09-30 11:35:00')
17.Combine DateADD and DateDIFF to get the last date of a month
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.