I encountered a problem at work today. I needed to query by time, but the query results were incorrect. For example, if you want 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, and you will find that the information about the members registered on 2007-10-31 is basically It doesn't show up, 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. Add dates
DateAdd function
Returns the date to which the specified time interval has been added.
DateAdd(interval, number, date)
The syntax of the DateAdd function has the following parameters
(1) interval is required. A string expression representing the time interval to add. See the Settings section for numerical 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.
set up
The interval parameter can have the following values:
Setting description
yyyy year
q quarter
m month
y the number of days in a year
d day
w Day of the week
ww weeks
h hours
n minutes
s seconds
illustrate
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 the current day 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 numerical 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 numerical 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 numerical values.
set up
The interval parameter can have the following values:
Setting description
yyyy year
q quarter
m month
y the number of days in a year
d day
w Day of the week
ww weeks
h hours
n minutes
s seconds
The firstdayofweek parameter can have the following values:
Constant value description
vbUseSystem 0 Use the National Language Support (NLS) API setting.
vbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday
The firstweekofyear parameter can have the following values:
Constant value description
vbUseSystem 0 Use the National Language Support (NLS) API setting.
vbFirstJan1 1 Starts with the week of January 1st (default).
vbFirstFourDays 2 starts with the first week of the new year that has at least four days.
vbFirstFullWeek 3 begins with the first full week of the new year.
illustrate
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 the number of days in a year (y) or days (d). When interval is the number of days in 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 weeks (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 parameter affects calculations using 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.