Addition and subtraction of dates using ASP

Source: Internet
Author: User
Tags date1
I encountered a problem at work today. I need to query by time, but the query results are incorrect. For example, if you want to find a member registered on the website between-10-12 and-10-31, select a date and click "query, it was found that the information of the registered members was not displayed at all. The results were the same after several tests. The debugging program found that there was a problem with the SQL statement.
The SQL statement is as follows: Select * From userinfo where regtime> = '2017-10-12 'and regtime <= '2017-10-31 '. It seems that this SQL statement is not wrong at first, but after comparing the values saved by the corresponding fields in the database, it is found that the saved values are not in the simple date format, but in the date + time format, that is: yyyy-mm-dd hh: mm: Ss. In this case, when the SQL statement determines the size of regtime and '2017-10-31 ', it will think that the write of '2017-10-31' is incomplete, therefore, these two values are not considered equal. What should I do?
Don't worry. asp provides us with the date addition and subtraction function to help us solve this problem.
1. Date Addition
Dateadd Function
Returns the date of the specified interval.
Dateadd (interval, number, date)
The syntax of the dateadd function includes the following parameters:
(1) interval is required. String expression, indicating the time interval to be added. For values, see the "Settings" section.
(2) number is required. Numeric expression, indicating the number of time intervals to be added. A numeric expression can be a positive number (to get the future date) or a negative number (to get the past date ).
(3) date is required. Variant or the text that represents the date to add interval.
Set
The interval parameter can have the following values:
Set description
Yyyy
Q quarter
M month
Y number of days in a year
D
Number of days per week
WW week
H hour
N minutes
S seconds

Description
The dateadd function can be used to add or subtract a specified time interval from a date. For example, you can use dateadd to start from the day 30 days later or 45 minutes later than the current day. To add a time interval in the unit of "day" To date, you can use "day of the year" ("Y") and "day" ("D ") or "number of days in a week" ("W ").

The dateadd function does not return an invalid date. In the following example, we will add a month to April January 31:

Newdate = dateadd ("M", 1, "31-Jan-95 ")
In this example, dateadd returns February 28, instead of February 31. If date is January 31, the return value is February 29, because 1996 is a leap year.

If the calculation date is before January 1, 100 AD, an error occurs.
If the number is not a long value, the first rounding is the nearest integer.
  
2. Date Subtraction
Datediff Function
Returns the interval between two dates.
Datediff (interval, date1, date2 [, firstdayofweek [, firstweekofyear])

The syntax of the datediff function includes the following parameters:
(1) interval is required. String expression used to calculate the time interval between date1 and date2. For values, see the "Settings" section.
(2) date1 and date2 are required. Date expression. Two dates used for calculation.
(3) firstdayofweek is optional. Specify the constant of the first day of the week. If not specified, the default value is Sunday. For values, see the "Settings" section.
(4) firstweekofyear is optional. Specifies the constant of the first week of a year. If this parameter is not specified, the default value is the week of April 1, January 1. For values, see the "Settings" section.

Set
The interval parameter can have the following values:
Set description
Yyyy
Q quarter
M month
Y number of days in a year
D
Number of days per week
WW week
H hour
N minutes
S seconds

The firstdayofweek parameter can have the following values:
Constant Value description
Vbusesystem 0 is set using the regional language (NLS) API.
Vbsunday 1 Sunday (default)
Vbmonday 2 Monday
Vbtuesday 3 Tuesday
Vbwednesday 4
Vbthursday 5 Thursday
Vbfriday 6 Friday
Vbsaturday 7 Saturday

The firstweekofyear parameter can have the following values:
Constant Value description
Vbusesystem 0 is set using the regional language (NLS) API.
Vbfirstjan1 starts from the week of July 1, January 1 (default ).
Vbfirstfourdays 2 starts from the first week of at least four days in the New Year.
Vbfirstfullweek 3 starts from the first complete week of the year.

Description
The datediff function is used to determine the number of specified time intervals between two dates. For example, you can use datediff to calculate the number of days with different dates, or the number of weeks between the day and the last day of the current year.

To calculate the number of days for date1 and date2, you can use "number of days in a year" ("Y") or "day" ("D "). When interval is "the number of days of a week" ("W"), datediff returns the number of weeks between two dates. If date1 is Monday, datediff calculates the number of Monday before date2. This result contains date2, not date1. If interval is "Week" ("ww"), the datediff function returns the number of weeks between two dates in the calendar table. Function compute the number of Sundays between date1 and date2. If date2 is Sunday, datediff calculates date2, but date1 is not calculated even if date1 is Sunday.

If date1 is later than date2, The datediff function returns a negative number.

The firstdayofweek parameter affects the calculation of the "W" and "ww" separators.

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 quotation marks ("") and the year is omitted, the current year will be inserted every time date1 or date2 expressions are calculated in the code. In this way, you can write program code for different years.

When interval is "year" ("YYYY"), although only one day is actually different from January 1, datediff returns 1, indicating that the year is different.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.