Two dates and time functions are useful for removing records based on date and time. Use the function dateadd () and dateDiff (), you can compare the day before. For example, how many hours have been entered in the following SELECT statement to display each record in the table:
SELECT Entrydate 'Time Entered'
Datediff (hh, entrydate, getdate ()) 'Hours ago' from Weblog
If the current time is from 6:15 on November 30, 2000, it will return the following results: Time Entered Hours AGO
....................................................
Dec 30 2000 4:09 PM 2 DEC 30 2000 4:13 PM 2 DEC 1 2000 4:09 PM 698 (3 ROW (S) Affected) Function Dadediff () parameter is three variables. The first variable specifies some part of the date. In this example, it is compared to the date on hours (to understand the details of each part of the date, please refer to Table 11.2) 689 between the specified time of November 1, 2000 and November 30, 2000 Hours. The other two parameters are 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 the data of the deadline. For example, assume that visitors must first register to use your site. After registration, they can use your site for a month. To determine when their free time will be used, you can use the following SELECT statement:
Select Username 'User Name',
Dateadd (mm, 1, firstvisit_date) 'registration expires'
The parameter of the from registration_table function dateAdd () has three variables. The first variable represents a certain part of the date (see Table 11.2), this example uses the MM of the menu. The second variable specifies the interval of time - in this example is one month. The last variable is a date, in this example, the date is taken from the datetime field firstvisit_date. Assume the current date is June 30, 2000, this statement will return as follows: User Name Registration Expires
...................................................................................
Bill Gates Jul 30 2000 4:09 PM President Clinton Jul 30 2000 4:13 PM William SHAKESPEARE JUL 1 2000 4:09 PM (3 row (s) affected) Note: In contrast to you, use the function dateadd () plus a date One month, it doesn't add 30 days. This function simply adds the month value to 1. This means that people registered in November will get more than 2 days or 3 days than those registered in February. To avoid this problem, you can use the function dateAdd () to add the number of days directly instead of the month.
