SQL Server/T-SQL/Date Timezone/DATEDIFF
Содержание
- 1 Combine DateADD and DateDIFF to get the last date of a month
- 2 DATEDIFF: date difference
- 3 DATEDIFF(day, "9-8-1989", "10-17-1991")
- 4 DATEDIFF(d, @thisdate, @nextmonth) "Days between"
- 5 DATEDIFF(m, "24 March 2001","24 March 1964")
- 6 DATEDIFF(mi, "24 March 2001","24 March 1964")
- 7 DATEDIFF(month, "9-8-1989", "10-17-1991")
- 8 DATEDIFF: return the difference between two dates
- 9 DATEDIFF(ss, @MyBirthDate, GETDATE())
- 10 DATEDIFF(wk, "24 March 2001","24 March 1964")
- 11 DATEDIFF(yyyy, "24 March 2001","24 March 1964")
- 12 Difference between the current time and UTC
- 13 Difference in weeks between 7-2-1996 and 8-4-1997
- 14 Find the number of days between 24th March 2001 and 24th March 1964
- 15 Passing a column name to the DATEDIFF() value argument
- 16 select DATEDIFF(hour, "06:46:45", "11:35:00")
- 17 select DATEDIFF(minute, "06:46:45", "11:35:00")
- 18 select DATEDIFF(quarter, "2001-12-01", "2002-09-30")
- 19 select DATEDIFF(second, "06:46:45", "11:35:00")
Combine DateADD and DateDIFF to get the last date of a month
23>
24> -- Creating a User-Defined Function
25>
26> /*
27~ Returns a date representing the last date
28~ of any given month.
29~ */
30> CREATE Function dbo.fn_LastOfMonth(@TheDate DateTime)
31> Returns DateTime
32> AS
33> BEGIN
34> DECLARE @FirstOfMonth DateTime
35> DECLARE @DaysInMonth Int
36> DECLARE @RetDate DateTime
37>
38> SET @FirstOfMonth = DATEADD(mm, DATEDIFF(mm,0,@TheDate), 0)
39> SET @DaysInMonth = DATEDIFF(d, @FirstOfMonth, DATEADD(m, 1, @FirstOfMonth))
40> RETURN DATEADD(d, @DaysInMonth - 1, @FirstOfMonth)
41> END
42> GO
1> select dbo.fn_LastOfMonth( getdate() )
2> GO
-----------------------
2006-10-31 00:00:00.000
(1 rows affected)
1> drop function dbo.fn_LastOfMonth;
2> GO
1>
2>
DATEDIFF: date difference
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> Drop procedure spInsertDate
3> GO
1>
2> CREATE PROC spInsertDate
3> @myDate datetime = NULL
4> AS
5> DECLARE @InsertedDate smalldatetime
6> IF DATEDIFF(dd,@myDate, GETDATE()) > 7
7> BEGIN
8> SELECT @InsertedDate = NULL
9> PRINT "Invalid Date"
10> PRINT "Supplied Date was greater than 7 days old."
11> PRINT "The value has been reset to NULL"
12> END
13> ELSE
14> BEGIN
15> SELECT @InsertedDate = CONVERT(datetime,(CONVERT(varchar,@myDate,112)))
16> PRINT "The Time of Day in Date was truncated"
17> END
18> INSERT INTO Employee (start_date) VALUES (@InsertedDate)
19> GO
1>
2> DECLARE @MyIdent int
3>
4> EXEC spInsertDate @myDate = "1/1/1999"
5>
6> SELECT * FROM Employee
7> GO
Invalid Date
Supplied Date was greater than 7 days old.
The value has been reset to NULL
(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
NULL NULL NULL NULL NULL NULL
(10 rows affected)
1>
2>
3> drop table employee
4> GO
1>
DATEDIFF(day, "9-8-1989", "10-17-1991")
1>
2> SELECT DATEDIFF(day, "9-8-1989", "10-17-1991")
3> GO
-----------
769
(1 rows affected)
1>
DATEDIFF(d, @thisdate, @nextmonth) "Days between"
3> DECLARE @thisdate datetime, @nextmonth datetime
4>
5> SET @thisdate = GETDATE()
6> SET @nextmonth = DATEADD(m, 1, GETDATE())
7>
8> SELECT CONVERT(varchar, @thisdate, 101) "Today",
9> CONVERT(varchar, @nextmonth, 101) "One month from today",
10> DATEDIFF(d, @thisdate, @nextmonth) "Days between"
11> GO
Today One month from today Days between
------------------------------ ------------------------------ ------------
10/12/2006 11/12/2006 31
(1 rows affected)
1>
DATEDIFF(m, "24 March 2001","24 March 1964")
1> SELECT DATEDIFF(m, "24 March 2001","24 March 1964")
2> GO
-----------
-444
(1 rows affected)
DATEDIFF(mi, "24 March 2001","24 March 1964")
1> SELECT DATEDIFF(mi, "24 March 2001","24 March 1964")
2> GO
-----------
-19460160
(1 rows affected)
DATEDIFF(month, "9-8-1989", "10-17-1991")
1> -- DATEDIFF() Function
2>
3> --There are four elements in this equation: the start date, the interval (date unit),
4> -- the difference value, and the end date.
5>
6> -- What the difference is between the dates 9-8-1989 and 10-17-1991 in months:
7>
8> SELECT DATEDIFF(month, "9-8-1989", "10-17-1991")
9> GO
-----------
25
(1 rows affected)
1>
2>
DATEDIFF: return the difference between two dates
1> -- DATEDIFF: return the difference between two dates.
2>
3> -- DATEDIFF(what_to_return,first_date,second_date)
4>
5> -- Return the number of days between 24th March 1964 and 2001, you would use,
6>
7> SELECT DATEDIFF(dd,"24 March 1964","24 March 2001")
8> GO
-----------
13514
(1 rows affected)
1>
DATEDIFF(ss, @MyBirthDate, GETDATE())
1> DECLARE @MyBirthDate DateTime
2> SET @MyBirthDate = "3-24-1967"
3> SELECT DATEDIFF(ss, @MyBirthDate, GETDATE())
4> GO
-----------
1248289384
(1 rows affected)
1>
DATEDIFF(wk, "24 March 2001","24 March 1964")
1> SELECT DATEDIFF(wk, "24 March 2001","24 March 1964")
2> GO
-----------
-1930
(1 rows affected)
DATEDIFF(yyyy, "24 March 2001","24 March 1964")
40>
41> SELECT DATEDIFF(yyyy, "24 March 2001","24 March 1964")
42> GO
-----------
-37
(1 rows affected)
Difference between the current time and UTC
1> -- Difference between the current time and UTC
2>
3> SELECT DATEDIFF(hour, GETDATE(), GETUTCDATE())
4> GO
-----------
7
(1 rows affected)
1>
2>
Difference in weeks between 7-2-1996 and 8-4-1997
1> -- Difference in weeks between 7-2-1996 and 8-4-1997?
2>
3> SELECT DATEDIFF(week, "7-2-1996", "8-4-1997")
4> GO
-----------
57
(1 rows affected)
1>
Find the number of days between 24th March 2001 and 24th March 1964
1> -- Find the number of days between 24th March 2001 and 24th March 1964 using
2>
3> SELECT DATEDIFF(dd, "24 March 2001","24 March 1964")
4> GO
-----------
-13514
(1 rows affected)
1>
Passing a column name to the DATEDIFF() value argument
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>
3> -- Passing a column name to the DATEDIFF() value argument: the approximate working time of each employee:
4>
5> SELECT Name, DATEDIFF(year, Start_Date, GETDATE())
6> FROM Employee
7> GO
Name
---------- -----------
Jason 12
Robert 11
Celia 10
Linda 9
David 8
James 7
Alison 6
Chris 5
Mary 4
(9 rows affected)
1>
2>
3>
4>
5> drop table employee
6> GO
1>
select DATEDIFF(hour, "06:46:45", "11:35:00")
2>
3> select DATEDIFF(hour, "06:46:45", "11:35:00")
4> GO
-----------
5
(1 rows affected)
select DATEDIFF(minute, "06:46:45", "11:35:00")
2>
3> select DATEDIFF(minute, "06:46:45", "11:35:00")
4> GO
-----------
289
(1 rows affected)
select DATEDIFF(quarter, "2001-12-01", "2002-09-30")
2>
3> select DATEDIFF(quarter, "2001-12-01", "2002-09-30")
4> GO
-----------
3
(1 rows affected)
select DATEDIFF(second, "06:46:45", "11:35:00")
2>
3> select DATEDIFF(second, "06:46:45", "11:35:00")
4> GO
-----------
17295
(1 rows affected)