в 13:46, 26 мая 2010

2010-05-26T13:46:06Z

Admin: 1 версия

2010-05-26T10:21:14Z

1 версия

Новая страница

==Combine DateADD and DateDIFF to get the last date of a month==



<source lang="sql">

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>

</source>

==DATEDIFF: date difference==



<source lang="sql">
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>

</source>

==DATEDIFF(day, "9-8-1989", "10-17-1991")==



<source lang="sql">
1>
2> SELECT DATEDIFF(day, "9-8-1989", "10-17-1991")
3> GO
-----------
769
(1 rows affected)
1>

</source>

==DATEDIFF(d, @thisdate, @nextmonth) "Days between"==



<source lang="sql">
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>

</source>

==DATEDIFF(m, "24 March 2001","24 March 1964")==



<source lang="sql">

1> SELECT DATEDIFF(m, "24 March 2001","24 March 1964")
2> GO
-----------
-444
(1 rows affected)

</source>

==DATEDIFF(mi, "24 March 2001","24 March 1964")==



<source lang="sql">

1> SELECT DATEDIFF(mi, "24 March 2001","24 March 1964")
2> GO
-----------
-19460160
(1 rows affected)

</source>

==DATEDIFF(month, "9-8-1989", "10-17-1991")==



<source lang="sql">
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>

</source>

==DATEDIFF: return the difference between two dates==



<source lang="sql">
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>

</source>

==DATEDIFF(ss, @MyBirthDate, GETDATE())==



<source lang="sql">

1> DECLARE @MyBirthDate DateTime
2> SET @MyBirthDate = "3-24-1967"
3> SELECT DATEDIFF(ss, @MyBirthDate, GETDATE())
4> GO
-----------
1248289384
(1 rows affected)
1>

</source>

==DATEDIFF(wk, "24 March 2001","24 March 1964")==



<source lang="sql">

1> SELECT DATEDIFF(wk, "24 March 2001","24 March 1964")
2> GO
-----------
-1930
(1 rows affected)

</source>

==DATEDIFF(yyyy, "24 March 2001","24 March 1964")==



<source lang="sql">

40>
41> SELECT DATEDIFF(yyyy, "24 March 2001","24 March 1964")
42> GO
-----------
-37
(1 rows affected)

</source>

==Difference between the current time and UTC==



<source lang="sql">

1> -- Difference between the current time and UTC
2>
3> SELECT DATEDIFF(hour, GETDATE(), GETUTCDATE())
4> GO
-----------
7
(1 rows affected)
1>
2>

</source>

==Difference in weeks between 7-2-1996 and 8-4-1997==



<source lang="sql">
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>

</source>

==Find the number of days between 24th March 2001 and 24th March 1964==



<source lang="sql">

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>

</source>

==Passing a column name to the DATEDIFF() value argument==



<source lang="sql">

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>

</source>

==select DATEDIFF(hour, "06:46:45", "11:35:00")==



<source lang="sql">

2>
3> select DATEDIFF(hour, "06:46:45", "11:35:00")
4> GO
-----------
5
(1 rows affected)
</source>

==select DATEDIFF(minute, "06:46:45", "11:35:00")==



<source lang="sql">

2>
3> select DATEDIFF(minute, "06:46:45", "11:35:00")
4> GO
-----------
289
(1 rows affected)
</source>

==select DATEDIFF(quarter, "2001-12-01", "2002-09-30")==



<source lang="sql">

2>
3> select DATEDIFF(quarter, "2001-12-01", "2002-09-30")
4> GO
-----------
3
(1 rows affected)
</source>

==select DATEDIFF(second, "06:46:45", "11:35:00")==



<source lang="sql">

2>
3> select DATEDIFF(second, "06:46:45", "11:35:00")
4> GO
-----------
17295
(1 rows affected)
</source>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL Server/T-SQL/Date Timezone/DATEDIFF - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия