SQL Server time format Analysis

In the database, time and date are often very important data. The time on each computer is often different. In order to insert a unified time in the database, it is best to read the time directly from the database server if it is the current time. For example, if there is a table named tablename, where the columnname field is the current time when the current record is inserted, the insert statement should be written as: insert into Table Name (columnname ,...) Values
(Getdate (),...). In this way, the getdate () function inserts the current time of the database server into the record.

Before looking for all the records of the day, analyze the time and date representation of the T-SQL. In the T-SQL, the time date format data type is actually a floating point number type, recording the current time to January 1, 1900 0 hours of days, plus the remaining time into decimal. The following statement:

Select getdate () as current time, cast (getdate () as float) as days from January 1, January 1, 1900

Will return:

 

Current Time
Days from January 1, January 1, 1900

---------------------------
-----------------------------------------------------

2001-11-10
11:05:35. 733
37203.462219135799

 

(1 row (s)
Affected)

 

Therefore, if you want to query the records inserted on the current day, you can use getdate () to get two current times and convert the previous one to an integer (recorded as inttoday) using the convert or cast function ), convert the last one to a floating point number (recorded as floatnow), and then convert the record date to a floating point number (recorded as floatcheck) as long as the condition is used "... Where floatcheck between inttoday and floatnow "can be used to find out which time belongs to today's time. It seems so now.

Table testtable has three fields. ID is an Automatically increasing primary key, inserttime is the time when the data is inserted, and comment is set for convenience. There are four records in this table. The first two records were inserted on April 9, November 9, and the last two records were inserted on April 9, 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 first record

2 2001-11-09
17:43:22. 503 second record

3 2001-11-10
11:29:11. 907 the third record

4 2001-11-10
11:29:51. 553 Fourth Record

 

(4 row (s) affected)

 

The following statement selects the records inserted today (November 10, 2001). The database server time for executing this statement is 11:40:57. 800

 

Select * From testtable where cast (inserttime as float)
Cast (getdate () as INT) and cast (getdate () as float)

 

ID
Inserttime comment

------------
--------------------------------------
--------------------------------------------

3 2001-11-10
11:29:11. 907 the third record

4 2001-11-10
11:29:51. 553 Fourth Record

(2 row (s) affected)

 

This statement successfully filters records generated the previous day.

At, the lunch time is up. I will wait for a while to continue working.

At, let me enjoy my "masterpiece" before I start my work ". However, when something goes wrong, the statement used for filtering does not return anything. Remove the WHERE clause and execute it. The original record is still in progress. That is to say, the records in the database no longer meet the conditions. There is no other way. Let's take a look at the changes in the conditions.

Execute the statement:

Select cast (inserttime as float) as floatcheck, cast (getdate () as INT)
As inttoday, cast (getdate () as float) as floatnow from testtable

Result returned:

 

Floatcheck inttoday floatnow

Bytes -----------------------------------------------------------------------------------

3702.43660814043
37204 3703.524545756176

3702.728274807101
37204 3703.524545756176

3703.478610030863
37204
3703.524545756176

3703.479068904322
37204
3703.524545756176

 

(4 row (s) affected)

 

Note that inttoday is larger than floatnow, which is why the conditions are no longer met. The original cast () function does not simply remove decimal places, but returns a rounded value. Therefore, the value returned in the afternoon (cast (getdate () as INT) is 1 greater than that returned in the morning. In the program, it is impossible to select SQL statement execution in the morning and afternoon, so it is necessary to perform mathematical processing on the value returned by getdate. Note that the value returned in the afternoon of cast (getdate () as INT) is as big as the value returned in the afternoon of the previous day. My solution is to first subtract the value of getdate () from 0.5. In this way, if it is the morning, after minus 0.5, it becomes the afternoon of the previous day, and the fractional part is "in". If it is the afternoon, after minus 0.5, it becomes the morning of the current day, and the fractional part is "go ". The new statement is as follows:

Select * From testtable where cast (inserttime as float)
Cast (Getdate ()-0.5As INT) and cast (getdate () as float)

The execution result is normal.

In addition, in fact, float (or real) data types are the basic types of datetime data types, so they can be compared transparently, that is, they can be directly compared without conversion, like this:

Select * From testtable where inserttime between cast (getdate () to 0.5
As INT) and getdate ()

The previous conversions were just for convenience.