I encountered a problem at work today. I needed to query by time, but the query results were incorrect. For example, to find out the members who registered on the website between 2007-10-12 and 2007-10-31, after selecting the date, click the "Query" button to find the members who registered on 2007-10-31. The information is not displayed at all, and the results are the same after several tests. The debugger found that there was a problem with the SQL statement.
The SQL statement is as follows: SELECT * FROM userinfo WHERE regtime >= '2007-10-12' AND regtime <= '2007-10-31'. At first glance, there seems to be no error in this SQL statement, but after comparing the values saved in the corresponding fields in the database, it is found that the saved values are not in the form of a simple date, but in the form of date + time, that is: yyyy-MM-dd HH:mm :ss, when the SQL statement determines the size of regtime and '2007-10-31', it will think that '2007-10-31' is incompletely written, so the two values will not be considered equal. What to do?
Don't worry, ASP provides us with date addition and subtraction functions to help us solve this problem.
1. Date addition
DateAdd function returns the date to which the specified time interval has been added.
DateAdd(interval, number, date)
The syntax of DateAdd function has the following parameters (1) interval is required. A string expression representing the time interval to add. See the Settings section for values.
(2) number is required. Numeric expression indicating the number of time intervals to be added. Numeric expressions can be positive (getting a date in the future) or negative (getting a date in the past).
(3) date is required. Variant or text representing the date to which interval is to be added.
Description You can use the DateAdd function to add or subtract a specified time interval from a date. For example, you can use DateAdd to calculate a date 30 days from today or a time 45 minutes from now. To add a time interval in "days" to date, you can use "days of the year" ("y"), "days" ("d"), or "days of the week" ("w").
The DateAdd function does not return invalid dates. The following example adds one month to January 31, 1995:
NewDate = DateAdd("m", 1, "31-Jan-95")
In this example, DateAdd returns February 28, 1995, not February 31, 1995. If the date is January 31, 1996, February 29, 1996 is returned because 1996 is a leap year.
If the calculated date is before AD 100, an error will occur.
If number is not a Long value, it is rounded to the nearest integer before calculation.
2. Date subtraction DateDiff function returns the time interval between two dates.
DateDiff(interval, date1, date2 [,firstdayofweek[, firstweekofyear]])
The syntax of the DateDiff function has the following parameters:
(1) interval is required. A string expression used to calculate the time interval between date1 and date2. See the Settings section for values.
(2) date1, date2 are required. Date expression. Two dates used for calculation.
(3) Firstdayofweek optional. A constant that specifies the first day of the week. If not specified, it defaults to Sunday. See the Settings section for values.
(4) Firstweekofyear optional. Constant that specifies the first week of the year. If not specified, it defaults to the week of January 1st. See the Settings section for values.
Description The DateDiff function is used to determine the number of specified time intervals that exist between two dates. For example, you can use DateDiff to calculate the number of days between two dates, or the number of weeks between today and the last day of the year.
To calculate the number of days between date1 and date2, you can use "days in year" ("y") or "days" ("d"). When interval is "day of the week" ("w"), DateDiff returns the number of weeks between two dates. If date1 is a Monday, DateDiff counts the number of Mondays before date2. This result contains date2 but not date1. If interval is "week" ("ww"), the DateDiff function returns the number of weeks between two dates in the calendar table. The function counts the number of Sundays between date1 and date2. DateDiff will evaluate date2 if date2 is a Sunday, but not date1 even if date1 is a Sunday.
If date1 is later than date2, the DateDiff function returns a negative number.
The firstdayofweek argument affects calculations using the "w" and "ww" separator symbols.
If date1 or date2 is a date literal, the specified year becomes a fixed part of the date. But if date1 or date2 is enclosed in quotation marks (" ") and the year is omitted, the current year will be inserted every time the date1 or date2 expression is evaluated in the code. This makes it possible to write program code that works for different years.
When interval is "year" ("yyyy"), compare December 31st with January 1st of the next year. Although the difference is actually only one day, DateDiff returns 1 indicating a difference of one year.