How to pick data from 1am to 1am, the previous day

Question

How to pick data from 1am to 1am, the previous day

Gaja

Right there with Babe

Points: 768
More actions
June 19, 2014 at 4:19 am

#292514

I want to get data from previous day 1 AM to 1 AM
DECLARE @startDate DateTime , @endDate DateTime ,@getdate datetime
SET @getdate-2 = getdate()
SET @startDate = ISNULL(@startDate , DATEADD(dd,-1,@getDate))
SET @endDate =ISNULL(@endDate ,@getDate)
SELECT *
FROM TABLE
Where datetime between @startDate and @endDate

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Is there a question?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gaja Right there with Babe Points: 768 More actions · Answer 2

Yes i want to fetch data 1 am to 1 Am yesterdays date

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Gaja (6/19/2014)
Yes i want to fetch data 1 am to 1 Am yesterdays date

That is still not a question, but anyway...

DECLARE @startDate DATETIME, @endDate DATETIME, @getdate-2 DATETIME;

SET @getdate-2 = GETDATE();

SET @startDate = DATEADD(DAY,-1,DATEADD(HOUR,1,CONVERT(DATETIME,CONVERT(DATE,GETDATE()))))

SET @endDate = DATEADD(HOUR,1,CONVERT(DATETIME,CONVERT(DATE,GETDATE())));

SELECT @getdate-2, @startDate, @endDate;

SELECT *

FROM TABLE

WHERE [datetime] BETWEEN @startDate AND @endDate;

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gaja Right there with Babe Points: 768 More actions · Answer 4

Thanks

find simple way

SET @getdate-2 = Convert(Varchar,@getDate,101)+' 01:00:00.000'

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

Gaja (6/19/2014)
Thanks
find simple way
SET @getdate-2 = Convert(Varchar,@getDate,101)+' 01:00:00.000'

I try to use date functions more instead of relying on string manipulation and implicit conversions, but that's a personal choice.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 6

Using TimeFromParts function.

DECLARE @startDate datetime , @endDate datetime ,@getdate date

SET @getdate-2 = getdate()-1

SET @startDate = convert(varchar(30),@getdate) + ' ' + convert(varchar(30),Timefromparts(01,0,0,0,0))

SET @endDate =@startDate+1

SELECT *

FROM TABLE

WHERE [datetime] BETWEEN @startDate AND @endDate;

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server