Notes on time conversion of getdate in SQL Server

Time:2020-10-10

 

In SQL server, sometimes when querying data, it is necessary to limit the query time range. At this time, you need to calculate the time as follows:

 

USE AdventureWorks2014;
GO
SELECT  *
FROM    HumanResources.Employee
WHERE   ModifiedDate > GETDATE() - 20;

 

 

For example, to get the time of the previous day, you can use the following SQL statement.

 

    SELECT GETDATE() 1, GETDATE();

 

 

As for why to write such SQL, first of all, it is very simple and convenient to write in this way, which is a little easier than using DateAdd function. In addition, it is often written in this way for temporary query.If the time granularity of adding and subtracting getdate is hours or minutes, you should pay special attention。 Put two SQL, you carefully taste the difference between the following two SQL

 

 

SELECT GETDATE() 1/24, GETDATE();

 

clip_image001[13]

 

 

SELECT GETDATE() 1.0/24, GETDATE();

 

 

clip_image002[13]

 

 

If you don’t pay attention to this detail, you will step on some pits. Only because 1 / 24 is an integer operation in SQL server, and the calculation result is 0, it must be converted to floating-point operation. Although there is no technical content, sometimes a lot of people accidentally dig pits, especially those who often switch between different types of databases, because Oracle database is different from SQL server. As shown below

 

 

 

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
 
Session altered.
 
SQL> select sysdate, sysdate -1/24 from dual;
 
SYSDATE             SYSDATE-1/24
------------------- -------------------
2020-03-28 11:32:27 2020-03-28 10:32:27
 
SQL> 
 
SQL> select 1/24 from dual;
 
      1/24
----------
.041666667

 

clip_image003[13]