0

I have a date range:

SET @declared_start_datetime = '11/02/2015 07:00:00'
SET @declared_end_datetime = '11/09/2015 18:00:00'

This was working until I realized it was pulling all records from 7:00 am on 2nd of November until 6:00 pm on November 9th.

What I need it to do is pull all records from those dates but exclude Saturday/Sunday and only between the hours of 7:00 and 18:00 for Monday-Friday.

I am using SQL Server going through a linked MYSQL server so I am using OPENQUERY. Here is an example of what I have so far.

DEClare @session varchar(max) = 'SELECT * FROM database.session WHERE CREATIONTIMESTAMP between ''''' + convert(nvarchar(50),@converted_start_datetime, 120) + ''''' and ''''' + convert(nvarchar(50),@converted_end_datetime, 120) + ''''''
SET @session = 'SELECT * INTO ##session FROM OPENQUERY(mxie,''' + @session + ''')'
exec(@session)

How do I exclude Saturday's and Sundays and adjust it to only pull the time where the business is open?

Edit:

DEClare @session varchar(max) = 'SELECT * FROM database.session WHERE ((CREATIONTIMESTAMP between ''''' + convert(nvarchar(50),@converted_start_datetime, 120) + ''''' and ''''' + convert(nvarchar(50),@converted_end_datetime, 120) + ''''')
                                                                            AND (TIME(CREATIONTIMESTAMP) between ''''' + convert(nvarchar(50),@business_opens_time, 108) + ''''' and ''''' + convert(nvarchar(50),@business_closes_time, 108) + '''''))'
SET @session = 'SELECT * INTO ##session FROM OPENQUERY(mxie,''' + @session + ''')'
exec(@session)
select * from ##session
drop table ##session

This doesn't exclude Saturdays or Sundays yet but it is currently returning 0 results. if I change the AND to OR it will return results but not what I am looking for.

I am looking for Monday - Friday from 7:00 am until 6:00 pm for each of the days.

James Wilson
  • 5,074
  • 16
  • 63
  • 122
  • You have date/time functions that you can leverage here to modify your WHERE clause to be more specific. Have you tried using any of them? – Mike Brant Nov 10 '15 at 16:49
  • @MikeBrant None that I can think of that would keep my where clause neat. I could re-write the where clause to have a bunch of AND/OR's but that would then also mean for every single row my where clause has to compute several functions on date/time which would slow it down considerably. Going through OPENQUERY and a Linked server I fear would push the time to run the report to several minutes having to execute so many DATE/TIME conversions. – James Wilson Nov 10 '15 at 17:01
  • Your `WHERE` clause will not be neat. Then again it sounds like your whole setup is not neat, but that's a subject well outside the bounds of this question. If you are looking for a magic bullet for your setup, there is not one. To get a "neat" solution would likely require significant refactoring of your DB schema, perhaps to include a date/time dimension table that you could join against to clean things up. – Mike Brant Nov 10 '15 at 19:26
  • @MikeBrant Any suggestions on the where clause? I have updated the initial question with the where clause i am currently using but it isn't working. I don't know MySQL I'm a SQL guy having to use a MySQL database. – James Wilson Nov 10 '15 at 20:03

0 Answers0