SQL Server/T-SQL/Date Timezone/DATEADD
Содержание
- 1 Add 12 minutes to 24th March 1964
- 2 Combine the CONVERT()and the DATEADD() functions to format a return date value nine months before September 8, 1989
- 3 DATEADD: add days to a date
- 4 DATEADD: add or subtract a number of days, months, or years from a specific date
- 5 DATEADD(Day, 90, "4-29-1988"): adds a specific number of date unit intervals to a date/time value
- 6 DATEADD(mi, -9000000, "4-30 AM"): 9,000,000 milliseconds before
- 7 DATEADD(second, 9000, "4-30 AM"): 9,000 seconds after
- 8 DATEADD(year, 18, "4-30 AM"): 18 years later
- 9 DATEADD(yy, -18, "4-30 AM"): 18 years before
- 10 SELECT DATEADD(dayofyear, 18, "4-30 AM")
- 11 SELECT DATEADD(hh,12,"24 March 1964"): Add 12 hours to 24th March 1964
- 12 SELECT DATEADD(m,12,"24 March 1964"): Add 12 months to 24th March 1964
- 13 SELECT DATEADD(mi, -30, "2005-30:00.000")
- 14 select DATEADD(month, -1, "2002-35:00")
- 15 SELECT DATEADD(quarter, 18, "4-30 AM")
- 16 SELECT DATEADD(wk,12,"24 March 1964"): Add 12 weeks to 24th March 1964
- 17 SELECT DATEADD(yy,12,"24 March 1964"): Add 12 years to 24th March 1964
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>