Comparison between MSSQL and access on Date

It is difficult to compare the date and time of access. I tried it several times before and it was not very successful,
You can only change the date field to the character type for character comparison,
Now I met again. After repeated tests, I finally found the cause,
It is easier to use the cdate function to compare the access date. Example:
Adoquery1. SQL. Text: = select * From tablename
Where (cdate (ptime)> = cdate (: S3) and (cdate (ptime) <= cdate (: S4 )))';
Adoquery1.parameters. paramvalues ['s3']: =
Formatdatetime ('yyyy-MM-DD 00:00:00 ', datetimepicker1.date );
Adoquery1.parameters. paramvalues ['s4 ']: =
Formatdatetime ('yyyy-MM-DD 23:59:59 ', datetimepicker2.date );
Adoquery1.open;
The format of the date field in the database is regular date (YYYY-MM-DD hh: NN: SS)
This method is used to compare dates separately. Otherwise, when we compare dates of> = 2005-10-26 and <= 2005-10-26 on the same day,
The data for this day will not be found.
Previously, SQL statements were directly generated. Although the generated SQL statements can be operated normally in access queries,
However, when you move to Delphi for execution, the system prompts an error message: the parameter object is incorrectly defined. Provide inconsistent or incomplete information.
After a reminder from a friend, Mr. Li, we found that the execution was unsuccessful because Delphi regarded the ":" In the time and the subsequent values as variable parameters.
Then, the statement passes smoothly after the value is assigned again by the parameter-based method.
When a value of the date type is directly assigned, a "#" sign is directly added before and after the value, and then compared with the date field in the database.
For example, select * From tablename where (cdate (ptime) >=# 2005-10-26 #);
Another method is to assign the value and convert it through the access cdate function, as shown in the preceding figure,
In this way, the comparison is normal. However, no matter which method is used, the string to be assigned must conform to the format allowed by the date type,
If the string is directly 20051026121000, the "#" or cdate function cannot be correctly converted to the date type.

MSSQL -- calculate the interval between two times
-----------------------------------------------------------------------
Select datediff (day, database field, getdate () from Table ==> you can calculate the interval between the time in the database and the current time.
If the current time is less than the database time, it will take a negative value.
Day is the number of days between them, and month is the number of months,
The datediff function calculates the time difference between the second date and the first date in the specified two dates.
In other words, it gets the interval between two dates. The result is equal to the signed integer value of date2-date1, in each date part.
If one or two date parameters are smalldatetime values, they are converted to datetime values internally for calculation.
For calculation, the second and millisecond values in the smalldatetime value are automatically set to 0.

 

Select dateadd (day, 3, database Field) from Table ==> dateadd function adds a time interval to a specified date
For example, if the fields in the Table store the dates of all books, the date must be postponed for three days.
You can use this function to update the time.
If the data type of the date parameter is smalldatetime, The result data type is also smalldatetime.
You can use dateadd to add seconds or milliseconds to the smalldatetime value, but only when the result date returned by dateadd is changed for at least one minute,
This makes sense.