CODEWITHSUNDEEP
sqlt-sqlselect
Daku Daku
Daku Daku

Reputation: 115

TSQL Date conditions

I have a table of events, constantly being updated, with a datetime column.

I want to get all events that start today as well as the ones that start 8 hours into the next day.

The idea is that people don't really check in the middle of the night for events, so we list them in the day before.

To get today's I do DATEDIFF(day,eventdate,GETDATE())=0 but I havn't figured out how to do the dateadd() for my case. I either get no rows or too many.

So the wanted result is:

From 00:00 on March 9 to 8:00 on March 10. (example only)

Upvotes: 2

Views: 731

Answers (5)

Adam Porad
Adam Porad

Reputation: 14471

You can try something like this.

The DateAdd documention page shows all the different date parts you can use (hour, day, seconds, etc.)

DECLARE @StartTimeWindow DATETIME, @EndTimeWindow DATETIME
SET @StartTimeWindow = DATEDIFF(DAY, 0, GETDATE())
SET @EndTimeWindow = DATEADD(HOUR, 32, @StartTimeWindow)

SELECT *
FROM EventTable
WHERE EventDate >= @StartTimeWindow
  AND EventDate <= @EndTimeWindow

Upvotes: 1

Taryn
Taryn

Reputation: 247880

You can also do it this way:

select *
from yourtable
WHERE EventDate >= Convert(varchar(10), getdate(), 101)
AND EventDate < CAST(Convert(varchar(10), DateAdd(d, 1, getdate()), 101) +  ' 08:00 AM' as datetime)

Then if you EventDate has the time on it, then you can convert the EventDate in the first part of your WHERE clause

select *
from yourtable
WHERE Convert(varchar(10), EventDate, 101) >= Convert(varchar(10), getdate(), 101)
AND EventDate < CAST(Convert(varchar(10), DateAdd(d, 1, getdate()), 101) +  ' 08:00 AM' as datetime)

Upvotes: 1

amit_g
amit_g

Reputation: 31270

SELECT
    Column1, Column2
FROM
    TableName
WHERE
    DateColumm BETWEEN Convert(Date, GETDATE()) AND DateAdd(hh, 32, Convert(smalldatetime, Convert(Date, GETDATE())))

Upvotes: 1

Eoin Campbell
Eoin Campbell

Reputation: 44306

You can use these to bound your query...

DECLARE @Start DateTime = CONVERT(nvarchar(10), GetDate(), 121)
DECLARE @End DateTime = DATEADD(Hour, +32, @Start)

Here's a Test Script

CREATE TABLE #Temp (Column1 DateTime)

INSERT INTO #Temp (column1) values (getdate()) -- it's 6pm now
INSERT INTO #Temp (column1) values (dateadd(hour, -24, getdate())) --6pm yesterday - outside window
INSERT INTO #Temp (column1) values (dateadd(hour, -12, getdate())) --6am today
INSERT INTO #Temp (column1) values (dateadd(hour, -5, getdate())) -- 1pm today
INSERT INTO #Temp (column1) values (dateadd(hour, +12, getdate())) -- 6am tomorrow - inside window
INSERT INTO #Temp (column1) values (dateadd(hour, +17, getdate())) -- 11am tomorrow - outside window

select * from #Temp

DECLARE @Start DateTime = CONVERT(nvarchar(10), GetDate(), 121)
DECLARE @End DateTime = DateAdd(Hour, +32, @Start)

SELECT * FROM #Temp WHERE Column1 > @Start AND Column1 < @End

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

It is better to not do any calculation on your column. Calculate the interval instead and fetch the rows that is in the interval. That way you can make use of a index on eventdate instead of doing a table scan. 

select SomeColumns
from YourTable
where eventdate >= dateadd(day, datediff(day, 0, getdate()), 0) and
      eventdate < dateadd(hour, 32, dateadd(day, datediff(day, 0, getdate()), 0))

Upvotes: 7

Related Questions