locked
return time range from current date using getdate() or current_timestamp RRS feed

  • Question

  • hello,

    i am trying to create automated reports which will run a specific time range from the current date and none of the solutions i have found for similar problems seem to be clicking with what i need.

    basically, my company received orders all day and we need to verify which ones come through as new orders. i want this report to be able to run every 2 hours starting from 8 am to 10 am, 10 am -12 pm, 12pm - 2pm, 2pm - 4pm and 4pm to 8am the next day for the final time range.  I know how to use SQL reporting services so deploying these wont be a problem, i just cant get the queries to work for me.

    the closest i have gotten is below, but its not what i need.  I came accross statements to return the top of the hour but dont exactly know what to do with it.  (SELECT DATEADD(hh,DATEPART(hh,GETDATE()),DATEADD(dd,0, DATEDIFF(dd,0,GETDATE()))))

    select * from (select orderdate,  orderdate, convert(char(5), orderdate, 114) as Time from orders

    where orderdate between '2012-06-19' and '2012-06-20')x

    where

    time between '08:00:00' and '12:00:00'  

    Tuesday, June 19, 2012 8:47 PM

Answers

  • I would make sp and schedule it for the time you said.

    Pass the parameter to the sp as current datetime and current datetime - 2 hours.


    Please Mark as answer if this answers your question Or Mark as helpful if you found this post was helpful. Trilok Negi

    Tuesday, June 19, 2012 9:18 PM

All replies

  • I would make sp and schedule it for the time you said.

    Pass the parameter to the sp as current datetime and current datetime - 2 hours.


    Please Mark as answer if this answers your question Or Mark as helpful if you found this post was helpful. Trilok Negi

    Tuesday, June 19, 2012 9:18 PM
  • what does sp mean?
    Tuesday, June 19, 2012 9:20 PM
  • nevermind, duh, that totally worked.  thanks for the help.
    Tuesday, June 19, 2012 9:26 PM