CODEWITHSUNDEEP
sql-serverssrs-2012
user2536008
user2536008

Reputation: 215

SQL SERVER 2008 Filter between two dates and two times

I have a DateTime column called “Date_Entered” and I need to filter between two dates and within these two dates to filter between two times (time might be of the next day). Here is my query:

Declare @StartDate DateTime 
Declare @EndDate DateTime 
Declare @StartTime varchar(8) 
Declare @EndTime varchar(8)

SELECT Date_Entered FROM MyTable
WHERE (Date_Entered BETWEEN '1/1/2014' AND '1/1/2015')
AND (CONVERT(varchar(8), Date_Entered, 108) >= '21:00:00' OR @StartTime IS NULL)
AND (CONVERT(varchar(8), Date_Entered, 108) <= '03:00:00' OR @EndTime IS NULL) --This time value is on the next day to cover a 6-hour period

The desired results should display all values that are within the date range (for 2014 year) and took place during the 6-hour range (night time):

2014-05-15 21:09:00
2014-08-12 02:45:00
2014-09-05 01:40:00

All the above happened during a date range (2014) and between the time of 9PM and 3AM on the next day. My problem is that I need to search for time that happens from 9PM and 3AM on the next day (for the same date range)

If I search for time range in the same day like from 21:00 to 23:59 I get the results, but when time goes over the next day then I won't get any result. Any idea how to do that in SQL.

SSRS 2012 is used to enter the value for the parameters.

The Date_Entered field is DateTime type that has date and a time.

Upvotes: 0

Views: 1160

Answers (2)

Arunprasanth K V
Arunprasanth K V

Reputation: 21931

If i understood your question correctly then the below code will give correct output

create table a
(enddate datetime
)
insert into a values ('2014-05-15 21:09:00')
insert into a values ('2014-08-12 02:45:00')
insert into a values ('2014-09-05 01:40:00')

Query 

SELECT enddate FROM a
WHERE (enddate BETWEEN '1/1/2014' AND '1/1/2015')
AND (CONVERT(varchar(8), enddate, 108) >= '21:00:00' )
or (CONVERT(varchar(8), enddate, 108) <= '03:00:00' )

Result 

May, 15 2014 21:09:00
August, 12 2014 02:45:00
September, 05 2014 01:40:00

Upvotes: 1

Chris Kessler
Chris Kessler

Reputation: 39

Is it possible for you to convert the separate date and time fields into a single datetime field? Here's a link to a relevant SO question that provides an option for you to convert your search criteria into a single field. By doing so, it should let you to search for times beyond midnight within a single query.

SQL Server convert string to datetime

Upvotes: 0

Related Questions