SQL Server Create a filter in a Datetime field, bringing the previous 24 hours (not counting the current time)

Question

Good evening, I’m having trouble putting in a date filter, I have a query where I bring the information of CD, Crane and Dtregistro (datetime) In this query I am already filtering the 24 hours between the current day and 24 hours before,

GRU_DT_REGISTRO between GETDATE()-1 and GETDATE()

In this Filter I try to set to bring me from the current date and time, the 24 hours before, but it brings data of 25 hours, ie 22:00 today until 22 hours yesterday, I would like you to display the previous 24 hours without counting the current Date, for example now are: 06/08/2017 22:15:00, so in my filter should have dates between 05/08/2017 22:00 & 06/08/2017 21:59:00 Can someone give me a hand:

I am using Sql Server 2014

Answer 1

Long live,

1st we must be very careful when dealing with dates. Although the code

SELECT GetDate()-1

automatically understand that it is -1 day, could perfectly consider -1 second, minute, hour, week, year, etc. Even for the sake of future reading, it is best to put explicitly.

So, first separate identify the maximum date:

DECLARE @DFin as smalldatetime=cast(concat(cast(GetDate() as date), ' ', Left(cast(GetDate() as time),2),':00') as smalldatetime)
DECLARE @DIni as smalldatetime=DateAdd(day,-1,@DFin)

SELECT *
FROM Tabela1
WHERE DataHora>=@DIni And DataHora<@DFin

Note that I have not used Between, because the Beteween includes the relevant values.

Attention: the CONCAT function with mixed data types only works to from SQL Server 2012

Answer 2

You can use the Function DATEADD() returns 1 day before the exact time of the filter execution. The function takes 3 arguments:

1. The interval ex:(day, Month, year and etc);
2. The number of days ex:(negative for subtraction, and positive for addition);
3. Reference date;

Try it this way:

GRU_DT_REGISTRO BETWEEN DATEADD(day, -1, GETDATE()) AND GETDATE()

You can use CAST to return only the date:

GRU_DT_REGISTRO BETWEEN CAST(DATEADD(day, -1, GETDATE()) AS DATE) AND CAST(GETDATE() AS DATE)