to get records between yesterday's 3pm to today's 3pm sql server

Question

I have a table with a datetime column, and I extract the records using a select statement, I m not sure with the 'where' part where I want only records from yesterday's 3pm to today's 3pm sql server. Please help.

Answer 1

You can use GETDATE() to get today and GETDATE() - 1 to get yesterdays date. Then you can remove the time part and add 15 hours i.e. to get 3pm to both the dates.

SELECT * FROM tableName
WHERE dateColumn BETWEEN DATEADD(hh,15,DATEADD(dd,DATEDIFF(dd,0,GETDATE()-1),0))
AND DATEADD(hh,15,DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0))

Answer 2

DATEADD(D,-1,GETDATE()) : Gives you last date

DATEADD(HOUR,.....) : Will add 15 hours (required 3:00 PM, so added 15 hour)

SELECT
    *
FROM TableName E
WHERE 
E.BirthDate>=DATEADD(HOUR,15, CAST(CAST(DATEADD(D,-1,GETDATE()) AS Date) AS datetime))
AND E.BirthDate<=DATEADD(HOUR,15, CAST(CAST(GETDATE() AS Date) AS datetime))

Answer 3

When you want to specify Date with time, you have to specify the datetime literal in ISO 8601 standard

ISO 8601 is supported by SQLServer 2000 onwards.

ISO 8601 standard for datetime is: YYYY-MM-DDThh:mm:ss.nnn[Z]

CREATE TABLE #testingTime(a int, timeofinsert datetime)
INSERT INTO #testingTime
values (1, '2016-09-28T17:05:00'),
(2,'2016-09-29T14:05:00'),
(3,'2016-09-29T18:05:00')

SELECT * FROM #testingTime WHERE timeofinsert 
> '2016-09-28T15:00:00' AND timeofinsert < ='2016-09-29T15:00:00'

For your specific question for yesterday and today 3PM. you can use the below code:

CREATE TABLE #testingTime(a int, timeofinsert datetime)
INSERT INTO #testingTime
values (1, '2016-09-27T17:05:00'),
(2,'2016-09-28T14:05:00'),
(3,'2016-09-28T18:05:00')


DECLARE @yesterday3PM datetime = convert(varchar(10),convert(date,getdate()-1)) + 'T15:00:00'
DECLARE @today3PM datetime = convert(varchar(10),convert(date,getdate())) + 'T15:00:00'

select @yesterday3PM, @today3PM
SELECT * FROM #testingTime WHERE timeofinsert 
> @yesterday3PM AND timeofinsert < = @today3PM