code for how SQL Server makes time comparisons

Example: DateDiff (Dd,add_time,getdate ()) not between 0 and 7

Select COUNT (*) fromtable where DATEDIFF ([second], ' 2004-09-18 00:00:18 ', ' 2004-09-18 00:00:19 ') > 0

Description

Select DATEDIFF (Day, time1, time2) corresponds to the following example statement


Select DATEDIFF (Day, ' 2010-07-23 0:41:18 ', ' 2010-07-23 23:41:18 ')

time1 > time2 is negative;

Time1 < time2 are positive;

[Day]: Only compare 2010-07-23 ignore 0:41:18 ' other empathy

The following distinctions:



Year: SELECT DATEDIFF ([year],time1, time2) return value:-6, indicating that the post minusMySQLTutorial in reverse.

Months: SELECT DATEDIFF ([month], time1, time2)


Days: SELECT DATEDIFF ([day], time1, time2)


Hours: SELECT DATEDIFF ([hour], time1, time2)

Seconds: SELECT DATEDIFF ([second], time1, time2)


With the function getdate (), you can get the current date and time. The function getdate () can be used as the default value for the Datedime type field. This is useful for saving the time when the record was inserted. To create a table whose records contain the current date and time, you can add a datetime field, specifying its default value as the return value of the function getdate (), as in this case:

CREATE TABLE Site_log (
Username VARCHAR (40),
Useractivity VARCHAR (100),
EntryDate DATETIME DEFAULT GETDATE ())

Convert Date and time
The return value of the function getdate () is displayed only to seconds. In fact, the SQL sever internal time can be accurate to the millisecond level (to be exact, it can be exactly 3.33 milliseconds).
To get the date and time in a different format, you need to use the function convert (). For example, when the following statement executes, the time displayed will include milliseconds:

SELECT CONVERT (VARCHAR), GETDATE (), 9)

Note the use of the number 9 in the example. This number indicates which date and time format to use when displaying the date and time. When this statement executes, the following date and time are displayed:
Nov 1997 3:29:55:170AM
(1 row (s) affected)

In the function convert () You can use a number of different styles of date and time formats. The following table shows all the formats.

Type of date and time:
Type value standard output
0 Default Mon dd yyyy hh:miam
1 USA mm/dd/yy
2 ANSI yy.mm.dd
3 British/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)
Ten USA Mm-dd-yy
JAPAN YY/MM/DD
ISO YYMMDD
Europe Default + milliseconds--dd mon yyyy
Hh:mi:ss:mmm (24h)
14-hh:mi:ss:mmm (24h)

Types 0, 9, and 13 always return four-bit years. For other types, to show the century, add the style value to 100. Types 13 and 14 return the time of the 24-hour clock. The months returned by type 0, 7, and 13 are expressed in three-bit characters (represented by November).

For each format listed in the table, you can add the type value plus 100来 to show the Year of the century (for example, 00 will be shown as 2000). For example, to display dates by Japanese standard, including century, you should use the following statement:

SELECT CONVERT (VARCHAR), GETDATE (), 111)

In this example, the function convert () converts the date format to be displayed as 1997/11/30

Date and time of extraction
In many cases, you might just want to get a part of the date and time, not the full date and time. To extract a specific part of a date, you can use the function datepart (), like this:

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 which part of the date to extract, and the second variable is the actual data. In this example, the function datepart () extracts the month, because MM represents the month. The following is the output of this SELECT statement:

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

The month posted column shows the months each site is queried for. The return value of the function datepart () is an integer. You can use this function to extract various parts of a date, as shown in the following table.

The parts of the date and their abbreviations
Date Part shorthand value
Year YY 1753--9999
Quarter QQ 1--4
month mm 1--12
Day of the 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 dates and times, it is useful to use the function datepart () to return integers. However, the query results (2,5) in the previous example are not very readable. To get the partial date and time in a more readable format, you can use the function datename (), as shown in the following example:

SELECT site_name ' site name '
Datename (mm,site_entrydate) ' Month Posted '
From Site_directory

The function datename () and the function datepart () receive the same parameters. However, its return value is a string, not an integer. Here is the result of the above example using Datename ():

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

You can also use the function Datenae () www.3ppt.com to extract one day in one weeks. The following example extracts a day of the week and a month in a date:

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)

Returns the date and time range
When you analyze the data in a table, you may want to take out data at a specific time. You may be on a particular day?? such as December 25, 2000?? Visitors are interested in the activities on your site. To take out this type of data, you might try to use a SELECT statement like this:

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

Don't do that. This SELECT statement does not return the correct record?? It will only return a record of the date and time that is 12/25/2000 12:00:00:000am. In other words, only records entered at 0 o'clock midnight will be returned.

The problem is that SQL Sever will replace the partial date and time with the full date and time. For example, when you enter a date but do not enter a time, SQL sever will add the default time "12:00:00:000am". When you enter a time, but do not enter a date, SQL sever will add the default date "Jan 1 1900".
To return to the correct record, you need to apply the date and time range. There is more than one way to do this. For example, the following SELECT statement would return 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 a record in the table with a date and time greater than or equal to 12/25/2000 12:00:00:000am and less than 12/26/2000 12:00:00:000am. In other words, it will correctly return every record entered on the day of Christmas 2000.
Another way is that you can use like to return the correct record. By including the wildcard character "%" in the date expression, you can match all times for a specific date. Here's an example:

SELECT * from weblog WHERE entrydate like ' Dec 25 2000% '

This statement can match the correct record. 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, a second, or even a record entered in a millisecond. However, if you use like to match seconds or milliseconds, you first need to convert the date and time to a more precise format using the function convert (see the "Convert Date and Time" section earlier).

Compare dates and times
Finally, there are two date and time functions that are useful for fetching records based on date and time. Using Functions DateAdd () and DateDiff (), you can compare dates sooner or later. For example, the following SELECT statement shows how many hours each record in a table has been entered:

SELECT entrydate ' time entered '
DATEDIFF (Hh,entrydate,getdate ()) ' Hours Ago ' from weblog

If the current time is November 30, 2000 6:15 P.M., the following results are returned:

Time entered Hours Ago
...........................................................
Dec 2 4:09pm
Dec 2 4:13pm
Dec 1 4:09pm 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 the hour (see table 11.2 for details on the dates), which is 689 hours between the specified time of November 1, 2000 and November 30, 2000. The other two parameters are the time to compare. In order to return a positive number, the earlier time should be given first.
The function DateAdd () adds two dates. This function is useful when you need to calculate data for the cutoff date. If you want to check the records of registered users one months ago, you can use the following SELECT statement:

SELECT username ' User Name ',
DATEADD (mm,1,firstvisit_date) ' Registration Expires '
From registration_table

The parameters of the function DateAdd () have three variables. The first variable represents a part of the date, and this example uses the MM that represents the month. The second variable specifies the interval of time?? In this example, it is one months. The last variable is a date, in this case, the date is taken from the DateTime field firstvisit_date. Assuming the current date is June 30,2000, this statement will return the following:

User Name Registration Expires
.......................................................................................
Bill Gates-4:09pm
President Clinton Jul 4:13pm
William Shakespeare Jul 1 4:09pm
(3 row (s) affected)

Attention:
Use the function DateAdd () to add a date to one months, it does not add 30 days. This function simply adds 1 to the month value.


Use the Employees table in the Northwind library as the use Case table.
Use of the 1.between...and statement:
Description: Between...and for specifying test scope
See the following examples:
The Execute SQL statement "Select HireDate from Employees" displays the following results:
HireDate
1992-05-01 00:00:00.000
1992-08-14 00:00:00.000
1992-04-01 00:00:00.000
1993-05-03 00:00:00.000
1993-10-17 00:00:00.000
1993-10-17 00:00:00.000
1994-01-02 00:00:00.000
1994-03-05 00:00:00.000
1994-11-15 00:00:00.000

From the result set above, the records of HireDate in "1993-10-17" to "1994-01-02" are searched, thenSQLThe statements are as follows:
SELECT HireDate
From Employees
WHERE hiredate between cast (1993-10-17 as DateTime) and cast (1994-01-02 as DateTime)
After executing the statement, the result is as follows:
HireDate
1993-10-17 00:00:00.000
1993-10-17 00:00:00.000
1994-01-02 00:00:00.000
Between...and and cast appear in the above SQL statement, where cast is a type conversion function: In this example, the string is converted to a date type value.
Use Between...and to search for "1993-10-17" to "1994-01-02" records in where.

2. The comparison size of two date values can be used < <= > >= operator, and DateDiff function
DATEDIFF function: DATEDIFF (datepart, StartDate, EndDate)
The value of the DatePart can be 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 examples:
Searches for the value of HireDate after the "1993-05-03" record, theThe SQL statements are as follows:
SELECT HireDate
From Employees
WHERE DateDiff (Day,cast (1993-05-03 as DateTime), HireDate) >0
After executing the statement, the result is as follows:
HireDate
1993-10-17 00:00:00.000
1993-10-17 00:00:00.000
1994-01-02 00:00:00.000
1994-03-05 00:00:00.000
1994-11-15 00:00:00.000
So DateDiff (Day,cast (1993-05-03 as DateTime), HireDate) is to subtract the value of HireDate by "day" minus cast (1993-05-03 as DateTime)
The value of the date is determined by the positive or negative value of the subtraction.

code for how SQL Server makes time comparisons