I encountered a problem at work today. You need to check it according to time, but the query results are not correct. For example, you need to find out members registered on the website from 2007-10-12 to 2007-10-31. After selecting the date, click the query button, and find that the information of the members registered in 2007-10-31 is fundamentally fundamental It was not displayed, and the results were the same after several tests. The debugging program found that there were problems with the SQL statement.
The SQL statement is as follows: Select * from userInfo where regime> = '2007-10-12' and regime <= '2007-10-31'. At the beginning, there is no error in this SQL statement, but after the value of the corresponding field in the database, the value of the saved value is not a simple date form, but the form of date+time, that is: yyyy-MM-DD HH: mm : SS. At this time, the SQL statement is judging the size of RetTime and '2007-10-31', and the "2007-10-31 'is incomplete, so these two values are equal. What can I do?
Don't worry, ASP provides us with a function of date and subtraction to help us solve this problem.
1. Added date
Datedd function
Return to the date of the specified time interval.
Datedd (interval, number, date)
The grammar of the dateadd function has the following parameters
(1) Interval must option. String expressions indicate the time interval to be added. For values, see the setting part.
(2) Number must option. Numerical expression indicates the number of time intervals to be added. Numerical expressions can be positive (get the future date) or negative number (get the past date).
(3) Date must be option. Variant may add the text of the interval indication date.
illustrate:
Can be used to add or subtract the specified time interval from the date with the dateadd function. For example, DateADD can be used for 30 days from the day of the day or the time after 45 minutes from now. To add a daily interval to Date, you can use the number of daily (Y), daily (d) or a week (W) of the number of daily (Y), daily (d) or a week.
Datedd function does not return an invalid date. The following example will add a month on January 31, 1995:
Newdate = dateadd (m, 1, 31-jan-95)
In this example, Dateadd returned February 28, 1995 instead of February 31, 1995. If Date is January 31, 1996, it returns February 29, 1996, because 1996 is a leap year.
If the calculation date is before 100 AD, an error will occur.
If Number is not a Long type value, the first four houses and five entries are the closest integer.
2. Date reduction
Datediff function
Returns the time interval between two dates.
Datediff (interval, date1, date2 [, firstDayOfweek [, firstweekoFyear]])
The grammar of the Datediff function has the following parameters:
(1) Interval must option. String expressions indicate a time interval between Date1 and Date2. For values, see the setting part.
(2) Date1, date2 must be option. Date expression. Two dates for calculation.
(3) FIRSTDAYOFWEK can be optional. The constant on the first day in the specified week. If it is not specified, the default is Sunday. For values, see the setting part.
(4) FIRSTWEEKOFYEAR options. Specify the constant of the first week of the year. If it is not specified, it is default to the week of January 1. For values, see the setting part.
illustrate:
The DateDiff function is used to determine the number of specified time interval between two dates. For example, DateDiff can be used to calculate the number of days difference between two dates, or the weekly number between the day and the last day of the year.
To calculate the number of days between Date1 and Date2, you can use the number of days (Y) or day (d) of one year. When Interval is the number of days (W) of the week (W), DateDiff returns the weekly number between two dates. If Date1 is Monday, the number of Datediff is calculated to Date2 before the number of Monday. This result contains Date2 without Date1. If the interval is week (ww), the Datediff function returns the weekly number between two dates in the calendar table. The number of functions between Date1 and Date2 is calculated. If Date2 is Sunday, Datediff will calculate Date2, but even if Date1 is Sunday, Date1 will not be calculated.
If Date1 is later than Date2, the DateDiff function returns negative numbers.
The firstDayOfweek parameter affects the calculation of the WW interval symbol.
If Date1 or Date2 is a date text, the specified year will become a fixed part of the date. However, if Date1 or Date2 is included in the quotation marks () and omitted the year, each time the Date1 or Date2 expression is calculated in the code, the current year will be inserted. In this way, you can write a program code suitable for different years.
When the interval is the year (yyyy), compared with January 1st on December 31st and the coming year, although there is actually only one day, Datediff returns 1 to indicate a year difference.