Addition and subtraction of SQL statement date format

I encountered a problem at work today. I need to query by time, but the query results are incorrect. For example, you need to find the range from-10-12 to-10-31.
After you select a date for a member registered on the website, click the "query" button and find that the information of the member registered in-10-31 is not displayed at all. The results are the same after several tests. DebuggingProgramSending
Now, it turns out that there is a problem in the SQL statement. Powered by 25175.net

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 value is not a simple date form, but a date + time
Format: 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 '2017-10-31' is not complete, so it will not consider this
The two values are equal. What should I do?

1. Date Addition
Inland transportation

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.

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 calculate the date 30 days from the current day or
45 minutes later. To date
Add the time interval in the unit of "day". 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.

SQL statement:

Select dateadd (MI, 15, getdate (), * From sys_user

Note: The current date plus 15 minutes

2. Date Subtraction

Datediff Function

Returns the interval between two dates.
Women's brand ranking
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.

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
When the value is "the number of days of a week" ("W"), datediff returns the number of weeks between two dates. If date1 is Monday, datediff is calculated to date2.
The number of previous Monday. This result contains date2, not date1. If interval is "Week" ("ww"), then datediff
The 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
Date2 is calculated, 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 enclosed in quotation marks
("") And the year is omittedCodeWhen the expression date1 or date2 is calculated, the current year is inserted. 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.

Example:

Select * From sys_user where datediff (HH, create_date, getdate ()> 1

Description: queries data in the User table that is greater than one hour from the current date minus the newly added time (create_date ).