Просмотр исходного текста страницы SQL Server/T-SQL/Date Timezone/DATEDIFF

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



   
  <!-- start source code -->
   
    <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==



   
  <!-- start source code -->
   
    <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")==



   
  <!-- start source code -->
   
    <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"==



   
  <!-- start source code -->
   
    <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")==



   
  <!-- start source code -->
   
    <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")==



   
  <!-- start source code -->
   
    <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")==



   
  <!-- start source code -->
   
    <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==



   
  <!-- start source code -->
   
    <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())==



   
  <!-- start source code -->
   
    <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")==



   
  <!-- start source code -->
   
    <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")==



   
  <!-- start source code -->
   
    <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==



   
  <!-- start source code -->
   
    <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==



   
  <!-- start source code -->
   
    <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==



   
  <!-- start source code -->
   
    <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==



   
  <!-- start source code -->
   
    <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")==



   
  <!-- start source code -->
   
    <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")==



   
  <!-- start source code -->
   
    <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")==



   
  <!-- start source code -->
   
    <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")==



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