CODEWITHSUNDEEP
sql-serverdatetimesql-server-2012
LuckySevens
LuckySevens

Reputation: 333

Select records created in a 24 hour time-frame

I'm trying to query our database to find all records that were created between 6am yesterday and 6am today. This will be run in a report at any point during the day so set times/dates are useless.

I have this so far:-

SELECT * FROM DaySummaryDetail DSD
WHERE DSD.FromDateTime BETWEEN DATEADD(DAY, -1, GetDate()) 
AND DATEADD(Day, 1, GetDate())

But obviously this only works for 24 hours ago from right now until right now. I can't figure out how to apply a time as well as date.

Every example I find online seems slightly different and uses set dates/times ie, >= 20/02/2015 06:00:00.

I normally use Oracle SQL which would simply work using this:-

ptt.mod_date_time >= TRUNC (SYSDATE - 1) - 2 / 24
AND ptt.mod_date_time <= TRUNC (SYSDATE - 1) + 22 / 24

This would return results from 10pm to 10pm but the format appears totally different in SQL Server.

Upvotes: 3

Views: 3538

Answers (6)

Tanner
Tanner

Reputation: 22753

You can get the datetime values you are after by doing the following:

SELECT DATEADD(HOUR,6,CONVERT(DATETIME, CONVERT(DATE ,GETDATE()))) Today6AM,
       DATEADD(HOUR,-18,CONVERT(DATETIME, CONVERT(DATE ,GETDATE()))) Yesterday6AM

By doing this: CONVERT(DATE ,GETDATE()) you are stripping off the time portion of today's date. Converting it back to datetime gives you midnight for today.

The query adds 6 hours to midnight of the current day for 6am today and subtracts 18 hours from midnight of the current day to give you 6am on the previous day.

Output:

Today6AM                 Yesterday6AM
================================================
2015-02-20 06:00:00.000  2015-02-19 06:00:00.000

So adding that to your query:

SELECT * 
FROM DaySummaryDetail DSD
WHERE DSD.FromDateTime 
      BETWEEN DATEADD(HOUR,-18,CONVERT(DATETIME, CONVERT(DATE ,GETDATE()))) 
          AND DATEADD(HOUR,6,CONVERT(DATETIME, CONVERT(DATE ,GETDATE())))

Upvotes: 3

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

In SQL Server 2012 you can use SMALLDATETIMEFROMPARTS to construct a datetime value that is today at 6am like this:

SMALLDATETIMEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), DAY(GETDATE()), 6, 0)

Output: 2015-02-20 06:00:00

then you can use the above expression in place of GETDATE() in the WHERE clause:

DECLARE @TodayAt6AM DATETIME = SMALLDATETIMEFROMPARTS(YEAR(GETDATE()), 
                                                      MONTH(GETDATE()), 
                                                      DAY(GETDATE()), 
                                                      6, 
                                                      0)
SELECT * 
FROM DaySummaryDetail DSD
WHERE DSD.FromDateTime BETWEEN DATEADD(DAY, -1, @TodayAt6AM) AND     
                               DATEADD(Day, 1, @TodayAt6AM)

Upvotes: 1

JohnH
JohnH

Reputation: 2133

This should help ...

SELECT DATEADD( hour, 6, CAST(CAST(GETDATE(), AS Date) AS DateTime) ) AS 'Today@6am'
SELECT DATEADD( hour, 6, CAST(CAST(GETDATE()-1, AS Date) AS DateTime) ) AS 'Yesterday@6am'

Upvotes: 1

Eduard Uta
Eduard Uta

Reputation: 2617

One solution would be like so:

select  *
from DaySummaryDetail DSD
where DSD.FromDateTime between cast(cast(cast(getdate()-1 as date) as varchar(30)) + ' 06:00:00.000' as datetime)
    and cast(cast(cast(getdate() as date) as varchar(30)) + ' 06:00:00.000' as datetime)

Upvotes: 1

A ツ
A ツ

Reputation: 1267

today 6am is 

dateadd(hour,6,cast(cast(getdate() as date) as datetime))

cast(getdate() as date) truncates the timepart, cast it back as datetime because dateadd won't add hours otherwise and add 6hours

Upvotes: 1

gpullen
gpullen

Reputation: 1133

DECLARE @StartTimestamp          datetime
DECLARE @EndTimestamp            datetime
DECLARE @HourPartOfSearchRange   nvarchar(6)

SET @HourPartOfSearchRange       = ' 06:30'
SET @StartTimestamp              = 
CAST((CONVERT(varchar(11), DATEADD(DAY,-1,@CurrentUTCDateTime), 106) + @HourPartOfSearchRange) AS datetime)

SET @EndTimestamp                = 
CAST((CONVERT(varchar(11), @CurrentUTCDateTime, 106) + @HourPartOfSearchRange) AS datetime)

SELECT * FROM dbo.Test Where Timestamp Between @StartTimestamp AND @EndTimestamp

Upvotes: 1

Related Questions