SQL Assistance Do I run a Query on a dabase using BETWEEN a date from 5PM to 7AM the next day?

user_8689945 0
Viewed 60

I need to return a ticket count from 5PM on one day to 7am the following day. I tried using the following query using DATEADD but it isn't returning any results:

SELECT * FROM dbo.TicketIncidents
WHERE IncidentReportedDt BETWEEN 
DATEADD(hh,17,CONVERT(VARCHAR(32), GETDATE(),110))
and DATEADD(hh,7,CONVERT(VARCHAR(32), GETDATE()+1,110))

does anyone have any suggestions to making this work? Your assistance is greatly appreciated.

sql-server sql
2 Answers

Try using CAST and adding 31 hours:

Declare @date as datetime = '2017-09-29 12:00:00.000'

SELECT * FROM dbo.TicketIncidents
WHERE IncidentReportedDt BETWEEN 
DATEADD(hour,17,cast(Cast(@date as date) as datetime))
and DATEADD(hour,31,cast(Cast(@date as date) as datetime))
user_71259380

I think you should use 

DATEADD(hh, 17, CONVERT(DATETIME, CONVERT(DATE, GETDATE())))

instead of 

DATEADD(hh,17,CONVERT(VARCHAR(32), GETDATE(),110))

because converting to varchar in a platform-dependant format a date to convert it again to a datetime is a non the best way to do it

In my italian server your syntax throw a conversion error from varchar to datetime

user_45238960
Related