Date/Time subtraction - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Date/Time subtraction

Author

Topic

blandow
Starting Member

6 Posts

Posted - 2011-04-12 : 15:20:57

I have a report that I manually run at 8:30am that returns records that have been updated since the previous day from 3:30pm..
-----------------------------------------------------------------------
{distribution_stop_information.datetime_updated} > DateTime (2011, 04, 11, 15, 30, 00)
-----------------------------------------------------------------------
I am trying to use this report as a business object that will autorun
every day at 8:30am. How do I change the code to only retrieve from between 17 hours ago until the current time when the report is launched. (8:30am)?

Thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-12 : 15:30:18

dateadd(dy,datediff(dy,0,getdate())-1,0) -- yesterday
dateadd(hh,15.5,dateadd(dy,datediff(dy,0,getdate())-1,0))) --yesterday at 3:30pm

dateadd(dy,datediff(dy,0,getdate()),0) -- today
dateadd(hh,8.5,dateadd(dy,datediff(dy,0,getdate()),0))) -- today at 8:30 am

--Sample
distribution_stop_information.datetime_updated between dateadd(hh,15.5,dateadd(dy,datediff(dy,0,getdate())-1,0))) and dateadd(hh,8.5,dateadd(dy,datediff(dy,0,getdate()),0)))

Corey

I Has Returned!!

blandow
Starting Member

6 Posts

Posted - 2011-04-12 : 15:50:06

Thanks for the quick reply --

Crystal is not liking this..it says "missing the )" right after hh..

{distribution_stop_information.stop_exception_code} <> "null" and
{distribution_stop_information.datetime_updated} > dateadd (hh,15.5,dateadd(dy,datediff(dy,0,getdate())-1,0))

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-12 : 16:34:54

Looks like I got too excited and left off some parens... updated previous post.

Corey

I Has Returned!!

blandow
Starting Member

6 Posts

Posted - 2011-04-13 : 16:08:04

Still erroring out on me....Could be that Crystal Reports is the issue

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-13 : 16:50:03