Newbie - Date Question - Please help.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Newbie - Date Question - Please help.

Author

Topic

edwardsen35
Starting Member

1 Post

Posted - 2009-03-25 : 12:42:58

I have been using SSRS for two days...

I am in the process of writing a report and looking for some assistance or a point in the right direction.

I work for a food company. Our production day runs from 5 am to 4:59:59 am the following day. For example, when I run a production report for 3/23 the report needs to pull data from 3/23/09 05:00:00 to 3/24/09 04:59:59. I hard coded the dates in the SQL query but I would like it to automatically run each morning and send me the data from the previous day of production. I know how to setup the subscription but I am lost on how to specify the dates.

Any help is greatly appreciated.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-25 : 13:39:11

I'm not an ~~SSIS~~ SSRS guy but if you just need the t-sql to get yesterday 5:00am to Today 4:59:59am then you can use this:


select dateadd(day, datediff(day, 1, getdate()), '5:00:00.000') as FromDate
       ,dateadd(day, datediff(day, 0, getdate()), '5:00:00.000') as ToDate

output:
FromDate                ToDate
----------------------- -----------------------
2009-03-24 05:00:00.000 2009-03-25 05:00:00.000

Then you can say in your sql code:

WHERE <yourDateColumn> >= FromDate
AND   <yourDateColumn> < ToDate

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources