DATE FUNCTION - 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

SQL Server 2012 Forums

Transact-SQL (2012)

DATE FUNCTION

Author

Topic

Blessed1978
Yak Posting Veteran

97 Posts

Posted - 2014-05-22 : 20:43:40

date function to calculate my employees salary between current date(which ever the current day is that i'm doing the calculation, i assume getdate()) and last 60 and also another date function that calculates salary for last 90 days

example select sum(salary) as employee salary from MYDEPARTMENT where
posting_date between getdate() and getdate()-60 -- is this correct

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-22 : 23:39:12

couple things -
for BETWEEN you should start with the earlier date first.
and getdate() will be NOW down to the millisecond so you just need to decide what you want if posting_date is exactly 60 days ago. If posting_date is at say 11:03 AM then you'll get different answers when you run the report at 10:30 AM then you would at 11:30 AM. So you probably want 60 days ago at 00:00:00.000 to tonight at 11:59:59.999 right? So this would cover the full 60 day range regardless of time of day:


where posting_date >= dateadd(day, datediff(day, 0, getdate()-60),0)  --60 days ago at 00:00:00.000
and    posting_date < dateadd(day, datediff(day, 0, getdate()+1),0) --tomorrow morning at 00:00:00.000

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources