Date Reduction Method in MSSQL

 

Code

1 declare @ DT datetime;
2 set @ dt = getdate ();
3 declare @ Table (Caption varchar (20), value datetime );
4
5 insert into @ table values ('0', @ DT );
6 insert into @ table values ('-1', @ dt-1 );
7 insert into @ table values ('-0.1', @ dt-0.1 );
8 insert into @ table values ('-0.01', @ dt-0.01 );
9 insert into @ table values ('-0.001', @ dt-0.001 );
10 insert into @ table values ('-0.0001', @ dt-0.0001 );
11 insert into @ table values ('-0.00001', @ dt-0.00001 );
12 insert into @ table values ('-0.000001', @ dt-0.000001 );
13 insert into @ table values ('-0.0000001', @ dt-0.0000001 );
14
15 select * From @ table
16

 

 

Display result:

 

Caption value
0 2010-05-05 18:50:03. 547
-1 18:50:03. 547
-0.1 16:26:03. 547
-0.01 18:35:39. 547
-0.001 18:48:37. 147
-0.0001 18:49:54. 907
-0.00001 18:50:02. 683
-0.000001 18:50:03. 463
-0.0000001 18:50:03. 540

 

Then, how is it calculated. People who are a little focused can immediately see that when-1, the date is just less than one day, so we can understand it as follows:

Date-1 = minus 1 day.

Next, you only need to switch to understand.

Date-0.1 = today's date minus 0.1Days.

It is 0.1 days. What is 0.1 days? Suddenly realized that it was added or subtracted by minute.

1 day equals to 24 hours multiplied by 60 minutes

0.1 equals to 24 hours multiplied by 60 minutes, and then multiplied by 0.1.

This is exactly the case!