SQLTeam.com | Weblogs | Forums

Query between 6AM to 6PM

sql2012
sql2008r2

#1

Hi, I am looking to pull records every 6pm and 6 am. let say today is 12/28/2017 6:00pm the records that I will pull will be from 6:00AM to 6:00pm of 12/28/2017. if tomorrow morning 12/29/2017 6:00am the records that I will get will be from 6:00pm of 12/28/2017 to 12/29/2017 6:00am . just what to know if this is possible also in SSRS. Thank you in advance.

Note : the report will run every 6:am and 6:pm


#2

Sure, you can schedule runs in report manager


#3

in SSRS I can run every 12 hrs and start time is 6:00PM or 6:00AM but how can I handle that in my query? what will be my where clause condition. thanks for the reply.

select *
from tbl_receiving
where createddatetime between @begdate and @enddate


#4

Ssrs produces reports not tables unless you mean the logging tables in the report server db


#5

If you actually do schedule the job to run every 12 hours at the 6's, then it's easy. Just subtract (using a negative DATEADD) 12 hours from the current date and time for the start and use the current date and time as the end.

 SELECT SomeColumns
   FROM dbo.tbl_receiving
  WHERE CreatedDateTime >= DATEADD(hh, -12,GETDATE())
    AND CreatedDateTime <  GETDATE()
;

#6

Thank you Jeff. I will try this code.


#7

You're welcome. Let us know how it works out for you.


#8

Hi Jeff,

how about 12AM in the morning and 12PM in the afternoon how can I handle in my query and what will be the schedule job in SSRS.


#9

Hi Jeff Please disregard my new requirements. I made an adjustments in SSRS schedule. the query is the same