The time date in the database is often a very important data. Time on each computer is often different, in order to insert a unified time in the database, if it is to take the current moment, it is best to read directly from the database server. For example, a table named TableName, where the ColumnName field is the current time when the current record was inserted, should be written as: INSERT into table name (ColumnName,...) VALUES (GetDate (),... )。 This getdate () function inserts the current time of the database server into the record.
Before you look up all the records for the day, first analyze the time and date representation of T-SQL. In T-SQL, the time date format data type is actually a floating-point type that records the number of days from the current time to January 1, 1900 0 o'clock, plus the remaining time to decimal. The following statement:
Select GETDATE () as current time, cast (getdate () as float) as from January 1, 1900 days
So to find the records that were inserted that day, it would be a good idea to get two current times with getdate () and convert the last convert or cast function to an integer (recorded as Inttoday) and convert the latter to a floating-point number (recorded as Floatnow). You can also convert the date of the record you want to find to a floating-point number (Floatcheck), as long as you use the condition "... where floatcheck between Inttoday and Floatnow" to find out which time belongs to today's time. It does seem so now.
The table testtable has three fields, the ID is an automatically growing primary key, Inserttime is the time when the insertion time is recorded, and the comment field is set up for easy observation. There are four records in the table, the first two records were inserted on November 9, and the last two were inserted on November 10.
The first SQL statement returns all the records in the table.
This statement successfully filtered the records that were generated the day before.
It's 11:51, it's lunch time, I have to wait a while before I can go on with my work.
12:26, let me first enjoy my "masterpiece" before starting work. However, there was a problem, and the statement used to filter nothing returned. Remove the WHERE clause and then execute, and the original record is still there. That means the records in the database no longer meet the conditions. There is no other way, let's see what the conditions have changed.
Execute statement:
Select cast (inserttime as float) as Floatcheck,cast (GetDate () as int) as Inttoday,cast (GetDate () as float) as Floatnow fro M testtable
Note that Inttoday is larger than Floatnow, which is why conditions are no longer satisfied. The original cast () function does not simply remove the decimal number but round it, so the afternoon (cast (GetDate () as int)) returns a value 1 larger than the morning return. It is not possible in the program to first determine the morning and afternoon and then choose the SQL statement execution, so it is necessary to getdate () the value returned to do a mathematical processing. Note that the value returned the same afternoon as cast (GetDate () as int) is as large as the value returned the previous afternoon, and my approach is to subtract the GetDate () value by 0.5. So if it is morning, minus 0.5 after the day before the afternoon, the decimal part of the "into", if it is afternoon, minus 0.5 later into the morning, the decimal part of "Go". The new statement is written as follows:
SELECT * FROM TestTable where cast (inserttime as float) between cast (GetDate () -0.5 as int) and cast (GetDate () as float)
The execution results are normal.
In addition, float (and possibly real) data types are the basic types of datetime data types, so they can be compared transparently, which means they can be directly compared without having to convert, like this:
SELECT * from TestTable where inserttime between cast (GetDate () -0.5 as int) and GetDate ()
The conversion I made earlier was only for the convenience of illustration.
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.
