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.

user2536008
  • 215
  • 1
  • 5
  • 15
  • 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. – Gordon Linoff Jan 15 '16 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 – Arunprasanth K V Jan 15 '16 at 18:47
  • I edited my question to provide sample data. The Date_Entered field is DateTime type that captures date and time. – user2536008 Jan 15 '16 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. – DiscipleMichael Jan 15 '16 at 19:00
  • @ArunprasanthKV is correct. Just change final and to OR of what you already had – DiscipleMichael Jan 15 '16 at 19:13
  • @DiscipleMichael i think one bracket issue will be there , i did not execute it – Arunprasanth K V Jan 15 '16 at 19:19
  • try this 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 (at)StartTime IS NULL) or(CONVERT(varchar(8), Date_Entered, 108) <= '03:00:00' OR (at)EndTime IS NULL) – Arunprasanth K V Jan 15 '16 at 19:20

2 Answers2

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
Arunprasanth K V
  • 20,733
  • 8
  • 41
  • 71
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

Community
  • 1
  • 1
Chris Kessler
  • 39
  • 5