0

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.

Improve this question
7
  • 1
    Edit your question and provide sample data and desired results. It is not clear why you have @StartDate with a time component and then a separate time component as well. Commented Jan 15, 2016 at 18:35
  • time field also contains date ?? or its just like 1,2,3.. 24 ??? it it is like that then it will not make any difference today's date and tomorrow's date Commented Jan 15, 2016 at 18:47
  • I edited my question to provide sample data. The Date_Entered field is DateTime type that captures date and time. Commented Jan 15, 2016 at 19:00
  • A time can't both be >21:00 and <03:00. You need to combine Date and Time into DateTime or change your query to account for the date. Commented Jan 15, 2016 at 19:00
  • @ArunprasanthKV is correct. Just change final and to OR of what you already had Commented Jan 15, 2016 at 19:13

2 Answers 2

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks a lot for your help!
0

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

Improve this answer

2 Comments

It is a single DateTime field.
His StartDate and Endate fields are to limit the days that he searches, not to indicate the timespan to search for on each day. In his post he's searching in a timespan everyday from 2014.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.