SQL Server/T-SQL/Date Timezone/DATEADD

Add 12 minutes to 24th March 1964

1>
2> -- Add 12 minutes to 24th March 1964
3>
4> SELECT DATEADD(mi,12,"24 March 1964")
5>
6> SELECT DATEADD(n,12,"24 March 1964")
7> GO
-----------------------
1964-03-24 00:12:00.000
(1 rows affected)
-----------------------
1964-03-24 00:12:00.000
(1 rows affected)
1>

Combine the CONVERT()and the DATEADD() functions to format a return date value nine months before September 8, 1989

1>
2> -- Combine the CONVERT()and the DATEADD() functions to format a return 
date value nine months before September 8, 1989:
5>
6> SELECT CONVERT(VarChar(20), DATEADD(m, -9, "9-8-1989"), 101)
7> GO
--------------------
12/08/1988
(1 rows affected)
1>

DATEADD: add days to a date

1> create 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>
2> insert into employee (ID, name,    salary, start_date, city,       region)
3>               values (1,  "Jason", 40420,  "02/01/94", "New York", "W")
4> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (2,  "Robert",14420,  "01/02/95", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (3,  "Celia", 24020,  "12/03/96", "Toronto",  "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (4,  "Linda", 40620,  "11/04/97", "New York", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (5,  "David", 80026,  "10/05/98", "Vancouver","W")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (6,  "James", 70060,  "09/06/99", "Toronto",  "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (7,  "Alison",90620,  "08/07/00", "New York", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (8,  "Chris", 26020,  "07/08/01", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (9,  "Mary",  60020,  "06/09/02", "Toronto",  "W")
3> GO
(1 rows affected)
1>
2> select * from employee
3> GO
ID          name       salary      start_date              city       region
----------- ---------- ----------- ----------------------- ---------- ------
          1 Jason            40420 1994-02-01 00:00:00.000 New York   W
          2 Robert           14420 1995-01-02 00:00:00.000 Vancouver  N
          3 Celia            24020 1996-12-03 00:00:00.000 Toronto    W
          4 Linda            40620 1997-11-04 00:00:00.000 New York   N
          5 David            80026 1998-10-05 00:00:00.000 Vancouver  W
          6 James            70060 1999-09-06 00:00:00.000 Toronto    N
          7 Alison           90620 2000-08-07 00:00:00.000 New York   W
          8 Chris            26020 2001-07-08 00:00:00.000 Vancouver  N
          9 Mary             60020 2002-06-09 00:00:00.000 Toronto    W
(9 rows affected)
1>
2> INSERT INTO Employee (ID, Start_Date)
3>             VALUES   (99, DATEADD(day,-1,GETDATE()))
4>
5> select * from Employee
6> GO
(1 rows affected)
ID          name       salary      start_date              city       region
----------- ---------- ----------- ----------------------- ---------- ------
          1 Jason            40420 1994-02-01 00:00:00.000 New York   W
          2 Robert           14420 1995-01-02 00:00:00.000 Vancouver  N
          3 Celia            24020 1996-12-03 00:00:00.000 Toronto    W
          4 Linda            40620 1997-11-04 00:00:00.000 New York   N
          5 David            80026 1998-10-05 00:00:00.000 Vancouver  W
          6 James            70060 1999-09-06 00:00:00.000 Toronto    N
          7 Alison           90620 2000-08-07 00:00:00.000 New York   W
          8 Chris            26020 2001-07-08 00:00:00.000 Vancouver  N
          9 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>

DATEADD: add or subtract a number of days, months, or years from a specific date

1>
2> -- DATEADD: add or subtract a number of days, months, or years from a specific date.
3>
4>
5> -- Add 12 days to 24th March 1964
6>
7> SELECT DATEADD(dd,12,"24 March 1964")
8>
9> SELECT DATEADD(d,12,"24 March 1964")
10> GO
-----------------------
1964-04-05 00:00:00.000
(1 rows affected)
-----------------------
1964-04-05 00:00:00.000
(1 rows affected)
1>

DATEADD(Day, 90, "4-29-1988"): adds a specific number of date unit intervals to a date/time value

1>
2> -- DATEADD(): adds a specific number of date unit intervals to a date/time value.
3>
4> SELECT DATEADD(Day, 90, "4-29-1988")
5> GO
-----------------------
1988-07-28 00:00:00.000
(1 rows affected)
1>
2>

DATEADD(mi, -9000000, "4-30 AM"): 9,000,000 milliseconds before

1>
2> -- 9,000,000 milliseconds before:
3>
4> SELECT DATEADD(mi, -9000000, "4-29-1988 10:30 AM")
5> GO
-----------------------
1971-03-20 10:30:00.000
(1 rows affected)
1>

DATEADD(second, 9000, "4-30 AM"): 9,000 seconds after

1> -- 9,000 seconds after:
2>
3> SELECT DATEADD(second, 9000, "4-29-1988 10:30 AM")
4> GO
-----------------------
1988-04-29 13:00:00.000
(1 rows affected)
1>

DATEADD(year, 18, "4-30 AM"): 18 years later

1>
2> -- 18 years later:
3>
4> SELECT DATEADD(year, 18, "4-29-1988 10:30 AM")
5> GO
-----------------------
2006-04-29 10:30:00.000
(1 rows affected)
1>
2>

DATEADD(yy, -18, "4-30 AM"): 18 years before

1>
2> -- 18 years before:
3>
4> SELECT DATEADD(yy, -18, "4-29-1988 10:30 AM")
5> GO
-----------------------
1970-04-29 10:30:00.000
(1 rows affected)
1>

SELECT DATEADD(dayofyear, 18, "4-30 AM")

 
9>
10>
11> SELECT DATEADD(dayofyear, 18, "4-29-1988 10:30 AM")
12> GO
-----------------------
1989-10-29 10:30:00.000
(1 rows affected)
-----------------------
1988-05-17 10:30:00.000
(1 rows affected)

SELECT DATEADD(hh,12,"24 March 1964"): Add 12 hours to 24th March 1964

1> -- Add 12 hours to 24th March 1964
2>
3> SELECT DATEADD(hh,12,"24 March 1964")
4>
5> GO
-----------------------
1964-03-24 12:00:00.000
(1 rows affected)
1>

SELECT DATEADD(m,12,"24 March 1964"): Add 12 months to 24th March 1964

1> -- Add 12 months to 24th March 1964
2>
3> SELECT DATEADD(m,12,"24 March 1964")
4>
5> SELECT DATEADD(mm,12,"24 March 1964")
6> GO
-----------------------
1965-03-24 00:00:00.000
(1 rows affected)
-----------------------
1965-03-24 00:00:00.000
(1 rows affected)
1>

SELECT DATEADD(mi, -30, "2005-30:00.000")

 
3>
4> SELECT DATEADD(mi, -30, "2005-09-01 23:30:00.000")
5> GO
-----------------------
2005-09-01 23:00:00.000
(1 rows affected)

select DATEADD(month, -1, "2002-35:00")

 

2>
3> select DATEADD(month, -1, "2002-09-30 11:35:00")
4> GO
-----------------------
2002-08-30 11:35:00.000
(1 rows affected)

SELECT DATEADD(quarter, 18, "4-30 AM")

 
3>
4>
5> SELECT DATEADD(quarter, 18, "4-29-1988 10:30 AM")
6> GO
-----------------------
1992-10-29 10:30:00.000
(1 rows affected)

SELECT DATEADD(wk,12,"24 March 1964"): Add 12 weeks to 24th March 1964

1> -- Add 12 weeks to 24th March 1964
2>
3> SELECT DATEADD(wk,12,"24 March 1964")
4>
5> SELECT DATEADD(wk,12,"24 March 1964")
6> GO
-----------------------
1964-06-16 00:00:00.000
(1 rows affected)
-----------------------
1964-06-16 00:00:00.000
(1 rows affected)
1>

SELECT DATEADD(yy,12,"24 March 1964"): Add 12 years to 24th March 1964

1> -- Add 12 years to 24th March 1964
2>
3> SELECT DATEADD(yy,12,"24 March 1964")
4>
5> SELECT DATEADD(yyyy,12,"24 March 1964")
6> GO
-----------------------
1976-03-24 00:00:00.000
(1 rows affected)
-----------------------
1976-03-24 00:00:00.000
(1 rows affected)
1>