Time comparison in SQL Server

Time comparison in SQL Server

Example:

Select count (*) from table where DATEDIFF ([second], '2017-09-18 00:00:18 ', '2017-09-18 00:00:19')> 0

Description

The example statement for select DATEDIFF (day, time1, time2) is as follows:

Select DATEDIFF (day, '2017-07-23 0:41:18 ', '2017-07-23 23:41:18 ')

Time1> time2 is a negative number;

Time1 <time2 is positive;

[Day]: only 0:41:18 is ignored.

See the following:

 

Year: select datediff ([year], time1, time2) return value:-6, which indicates that it is the opposite of mysql tutorial before subtraction.

Month: select datediff ([month], time1, time2)

Day: select datediff ([day], time1, time2)

Hour: select datediff ([hour], time1, time2)

Seconds: select datediff ([second], time1, time2)

By using the GETDATE () function, you can obtain the current date and time. The GETDATE () function can be used as the default value of a DATEDIME field. This is useful for saving the time when a record is inserted. To create a table with records containing the current date and time, you can add a DATETIME field and specify its default value as the return value of the function GETDATE (), as shown in the following figure:

Create table site_log (
Username VARCHAR (40 ),
Useractivity VARCHAR (100 ),
Entrydate datetime default getdate ())

Conversion Date and time
The return value of the GETDATE () function is only displayed in seconds. In fact, the internal time of the SQL Server can be accurate to milliseconds (to be exact, it can be accurate to 3.33 milliseconds ).
To get the date and time in different formats, you need to use the CONVERT () function (). For example, when the current statement is executed, the display time includes milliseconds:

Select convert (VARCHAR (30), GETDATE (), 9)

Note the use of number 9 in the example. This number specifies the date and time format used for displaying the date and time. When this statement is executed, the following date and time are displayed:
Nov 30 1997 3: 29: 55: 170AM
(1 row (s) affected)

In function CONVERT (), you can use many different date and time formats. The following table shows all formats.

Date and time types:
Type value standard output
0 Default mon dd yyyy hh: miAM
1 USA mm/dd/yy
2 ANSI yy. mm. dd
3 Briish/French dd/mm/yy
4 German dd. mm. yy
5 Italian dd-mm-yy
6-dd mon yy
7-mon dd, yy
8-hh: mi: ss
9 Default + milliseconds -- mon dd yyyy
Hh: mi: ss: mmmAM (or)
10 USA mm-dd-yy
11 JAPAN yy/mm/dd
12 ISO yymmdd
13 Europe Default + milliseconds -- dd mon yyyy
Hh: mi: ss: mmm (24 h)
14-hh: mi: ss: mmm (24 h)

Type 0, 9, and 13 always return the year of four digits. For other types, to display the century, add the style value to 100. Types 13 and 14 return the 24-hour clock time. The values of the types 0, 7, and 13 are represented by three characters (Nov represents November ).

For each format listed in the table, you can add 100 to the type value to display the year of the century (for example, year 00 will be shown as 2000 ). For example, to display dates according to Japanese standards, including Century, you should use the following statement:

Select convert (VARCHAR (30), GETDATE (), 111)

In this example, the function CONVERT () converts the date format to 1997/11/30.

Extraction date and time
In many cases, you may only want to get part of the date and time, rather than the complete date and time. To extract specific parts of a date, you can use the DATEPART () function, as shown in the following code:

SELECT site_name 'site name ',
DATEPART (mm, site_entrydate) 'month posted' FROM site_directory

The parameter of the function DATEPART () is two variables. The first variable specifies the part of the date to be extracted, and the second variable is the actual data. In this example, the DATEPART () function extracts the month because mm represents the month. The output result of this SELECT statement is as follows:

Site Name Month Posted
........................................................................
Yahoo 2
Microsoft 5
Magicw3 5
(3 row (s) affected)

The Month Posted column shows the Month in which each site is queried. The return value of the DATEPART () function is an integer. You can use this function to extract different parts of a date, as shown in the following table.

Each part of the date and its abbreviation
Abbreviated date value
Year yy 1753--9999
Quarter qq 1--4
Month mm 1--12
Day of year dy 1--366
Day dd 1--31
Week wk 1--53
Weekday dw 1--7 (Sunday -- Saturday)
Hour hh 0--23
Minute mi 0--59
Second ss 0--59
Milisecond MS 0--999

When you need to compare the date and time, it is useful to return an integer using the DATEPART () function. However, the query result () in the previous example is not very readable. To obtain partial dates and times in a more readable format, you can use the DATENAME () function, as shown in the following example:

SELECT site_name 'site name'
DATENAME (mm, site_entrydate) 'month posted'
FROM site_directory

The functions DATENAME () and DATEPART () receive the same parameters. However, its return value is a string rather than an integer. The following is the result of the preceding example using DATENAME:

Site Name Month Postec
............................................................................
Yahoo February
Microsoft June
Magicw3 June
(3 row (s) affected)

You can also use the DATENAE () function to extract a day of a week. In the following example, a month in a week and a month in a date are extracted simultaneously:

SELECT site_name 'site name ',
DATENAME (dw, site_entrydate) + '-' + DATENAME (mm, site_entrydate)
'Day and Month posted' FORM site_directory

When this example is executed, the following results are returned:

Site Name Day and Month Posted
.................................................................................
Yahoo Friday-February
Microsoft Tuesday-June
Magicw3 Monday-June
(3 row (s) affected)

Return date and time range
When you analyze the data in the table, you may want to retrieve the data at a specific time. You may be interested in a specific day ?? For example, December 25, 2000 ?? Visitors are interested in activities on your site. To retrieve this type of data, you may try to use the SELECT statement as follows:

SELECT * FROM weblog WHERE entrydate = "12/25/20000"

Do not do this. This SELECT statement does not return the correct record ?? It returns only records with the date and time of 12/25/2000 12: 00: 00: 000 AM. In other words, only records entered at midnight are returned.

The problem is that SQL server replaces partial dates and times with complete dates and times. For example, if you enter a date but do not enter a time, SQL Server will add the default time "12: 00: 00: 000 AM ". When you enter a time, but do not enter a date, SQL Server will add the default date "Jan 1 1900 ".
To return the correct record, you need to apply the date and time range. There are more than one way to do this. For example, the following SELECT statement returns the correct record:

SELECT * FROM weblog
WHERE entrydate> = "12/25/2000" AND entrydate <"12/26/2000"

This statement can complete the task because it selects the date and time in the table that is equal to or greater than 12/25/2000 12: 00: 00: 000 AM and smaller than 12/26/2000 12: 00: 00: 000 AM Records. In other words, it will correctly return every record entered on the Christmas Day, January 1, 2000.
Another way is to use LIKE to return the correct record. By adding the wildcard "%" to a date expression, you can match all the times of a specific date. Here is an example:

SELECT * FROM weblog WHERE entrydate LIKE 'Dec 25 123'

This statement can match the correct records. Because the wildcard "%" represents any time.
Using these two functions that match the date and time range, you can select a month, a day, a year, an hour, a minute, or a second, even records input within a certain millisecond. However, if you use LIKE to match seconds or milliseconds, you must first use the CONVERT () function () convert date and time to a more accurate format (see the previous section "convert date and time ).

Comparison date and time
Finally, there are two date and time functions that are useful for retrieving records based on date and time. Using the functions DATEADD () and DATEDIFF (), you can compare the date morning and evening. For example, the following SELECT statement shows how many hours each record in the table has been input:

SELECT entrydate 'time entered'
DATEDIFF (hh, entrydate, GETDATE () 'hours Ago 'FROM weblog

If the current time is November 30, 2000 06:15 P.M., the following result is returned:

Time Entered Hours Ago
...........................................................
Dec 30 2000 PM 2
Dec 30 2000 PM 2
Dec 1 2000 PM 698
(3 row (s) affected)

The parameter of the function DADEDIFF () is three variables. The first variable specifies a part of the date. In this example, the date is compared by hour. (for details about each part of the date, refer to Table 11.2) there are November 1, 2000 hours between the specified time on January 1, November 30, 2000 and January 1, 689. The other two parameters are the time for comparison. To return a positive number, you should first give it an earlier time.
The DATEADD () function adds two dates. This function is useful when you need to calculate the data of the end date type. If you want to query the records of registered users a month ago, you can use the following SELECT statement:

SELECT username 'user name ',
DATEADD (mm, 1, firstvisit_date) 'registration exists'
FROM registration_table

The DATEADD () function has three variables. The first variable represents a part of the date. In this example, mm is used to represent the month. The second variable specifies the time interval ?? In this example, it is a month. The last variable is a date. In this example, the date is taken from the DATETIME field firstvisit_date. If the current date is June, this statement returns the following content:

User Name regires Expires
.......................................................................................
Bill Gates Jul 30 2000 PM
President Clinton Jul 30 2000 PM
William Shakespeare Jul 1 2000 PM
(3 row (s) affected)

Note:
Use the DATEADD () function to add a date to a month without adding 30 days. This function simply adds the month value to 1.

Use the employees table in the northwind database as the use case table.
1. Use the between... and statement:
Description: between... and is used to specify the test scope.
See the following example:
Execute the SQL statement "SELECT hiredate FROM employees" and the result is as follows:
Hiredate
00:00:00. 000
1992-08-14 00:00:00. 000
00:00:00. 000
00:00:00. 000
1993-10-17 00:00:00. 000
1993-10-17 00:00:00. 000
00:00:00. 000
1994-03-05 00:00:00. 000
1994-11-15 00:00:00. 000

Search for records with hiredate from "1993-10-17" to "1994-01-02" in the preceding result set. The SQL statement is as follows:
SELECT hiredate
FROM employees
WHERE hiredate between cast (1993-10-17 as datetime) and cast (1994-01-02 as datetime)
The result is as follows:
Hiredate
1993-10-17 00:00:00. 000
1993-10-17 00:00:00. 000
00:00:00. 000
Between... and cast appear in the preceding SQL statement. cast is a type conversion function. In this example, the string is converted to a date value.
Use between... and in the WHERE clause to search records from "1993-10-17" to "1994-01-02.

2. You can use the <<=>= operator and the datediff function to compare two date values.
Datediff function: DATEDIFF (datepart, startdate, enddate)
Datepart can be set to year, quarter, Month, dayofyear, Day, Week, Hour, minute, second, millisecond.
Startdate is subtracted from enddate. If startdate is later than enddate, a negative value is returned.
See the following example:
Search for records whose hiredate value is after "". The SQL statement is as follows:
SELECT hiredate
FROM employees
WHERE datediff (day, cast (1993-05-03 as datetime), hiredate)> 0
The result is as follows:
Hiredate
1993-10-17 00:00:00. 000
1993-10-17 00:00:00. 000
00:00:00. 000
1994-03-05 00:00:00. 000
1994-11-15 00:00:00. 000
Therefore, datediff (day, cast (as datetime), hiredate) converts the value of hiredate to "day" minus cast (as datetime)
To determine the order of dates by determining the plus or minus values.