Analysis of SQL Server Time Format
The date of 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, it is best to read the database server directly if you take the current time. For example, there is a table name TABLENAME, where the columnName field is current time when the current record is inserted, then the insert statement should be written as: INSERT INTO TABLE NAME (ColumnName, ...) Values (getdate (), ...). Such a getDate () function plugs the current time of the database server into the record.
Before finding all the records of the day, first analyze the T-SQL time date representation. In T-SQL, the time date format data type is actually a floating point type type, which is recorded at the current time to 0:00 on January 1, 1900, plus the remaining timing into a decimal. The following statement:
Select getdate () AS Current time, Cast (getdate () as float) AS is from January 1, 1900
Will return:
Current time from January 1, 1900
------------------------------------------------- ------------------------------
2001-11-10 11: 05: 35.733 37203.462219135799
(1 row (s) affected)
So look for records that the day insertion, you should think of getting two current times with getDate (), and convert the previous convert or CAST function to an integer (remember intertay), will convert the latter to floating point ( Remember to floatnow), then the record date that needs to be found is also converted to floating point numbers (recorded as floatcheck), as long as the condition "... where floatcheck between INTTODAY and floatnow" can find which time is today. It looks like this now.
Table TestTable has three fields, ID is an automatic primary key, INSERTTIME is the time of the record, and the Comment field is to facilitate observation. There are four records in this form. The first two records were inserted on November 9, and the last two were inserted on November 10.
The first SQL statement returns all records in the table.
SELECT * from TestTable
ID InsertTime Comment
------------ -------------------------------------- --------------------------------------------
1 2001-11-09 10: 28: 42.943 1 record
2 2001-11-09 17: 43: 22.503 2 Record
3 2001-11-10 11: 29: 11.907 Article 3 Record
4 2001-11-10 11: 29: 51.553 Article 4 Record
(4 row (s) affected)
The following language is trying to select the record inserted today (November 10, 2001). The database server time is executed by the statement is 2001-11-10 11: 40: 57.800select * from testtable where cast (InsertTime as float) Between Cast (getDate () as int) and cast (getdate () as float
ID InsertTime Comment
------------ -------------------------------------- --------------------------------------------
3 2001-11-10 11: 29: 11.907 Article 3 Record
4 2001-11-10 11: 29: 51.553 Article 4 Record
(2 row (s) affected)
This statement successfully filtered the records produced before the day.
Now at 11:51, the lunch time is here, I have to wait for a while to continue.
At 12:26, let me first admire my "masterpieces" before the start of work. However, there is a problem, that the statement used to filter is not returned. Remove the WHERE clause to execute, the original record is still there. In other words, the records in the database are no longer satisfied. There is no other way to let us see what changes have changed.
Execute a statement:
SELECT CAST (INSERTTIME AS FLOAT) AS FLOATCHECK, CAST (Getdate () AS INT (Getdate () as float) as floatnow from testtable
The result is returned:
FLOATCHECK INTTODAY FLOATNOW
-------------------------------------------------- ---------------------------------
3702.43660814043 37204 3703.524545756176
3702.728274807101 37204 3703.524545756176
3703.478610030863 37204 3703.524545756176
3703.479068904322 37204 3703.524545756176
(4 row (s) affected)
Notice that INTTODAY is bigger than floatnow, which is the reason why the conditions are no longer satisfied. The original CAST () function does not simply remove the decimal, but it is rounded in the afternoon, so the value returned by the afternoon than the morning. It is impossible to determine the SQL statement in the morning and in the afternoon, so it is necessary to make a mathematical process to the value returned by GetDate (). Note that the value returned in the afternoon and the value returned in the afternoon is the same, and my way is to subtract the getDate () value to 0.5. This way, if it is the afternoon, the decimal part "into" after minus 0.5, if it is in the afternoon, the decrease of 0.5 is changed to the same day, the fractional part "go". The new statement written is as follows: Select * from testtable where cast (INSERTTIME As Float) Between Cast (GetDate () - 0.5 as int) and cast (getdate () as float
The result is normal.
In addition, in fact, Float (or the REAL) data type is the basic type of the DateTime data type, so they can be transparent between them, which means that they can be directly compared, like this:
Select * from testtable where INSERE INSERE INSERETTIME BETWEEN CAST (GETDATE () - 0.5 as int) and getdate ()
My previous conversion is just for convenience of explanation.
