Problem with getdate()

[sap1958]

select distinct paykey,sum(payamt)as AmtDue
from payments
where datediff(month,payduedate,getdate())>=0
and payid='01101'
--just pulling one record normally leave this line out

Here is desired sample output
paykey AmtDue
0111 25

I used a date parameter of <=11/1/10 to pull records with a due date <=11/1/10
For record 01101, the getdate is todays date or 9/28/10
the paydue date is 10/1/10. For some reason record 01101 is not showing up. I believe the problem rests with the datediff formula. Any ideas

 

[gmmastros]

The DateDiff function actually returns the number of "transitions" based on the first argument. In this case, you are using month, and month transitions occur on the first day of the month at midnight.

For example:

Declare @StartDate DateTime
Declare @EndDate DateTime

Set @StartDate = '20091231 23:59:59'
Set @EndDate = '20100101 00:00:00'

Select 'Millisecond', DateDiff(Millisecond, @StartDate, @EndDate) Union All
Select 'Second',      DateDiff(second, @StartDate, @EndDate) Union All
Select 'Minute',      DateDiff(Minute, @StartDate, @EndDate) Union All
Select 'Hour',        DateDiff(Hour, @StartDate, @EndDate) Union All
Select 'Day',         DateDiff(Day, @StartDate, @EndDate) Union All
Select 'Week',        DateDiff(Week, @StartDate, @EndDate) Union All
Select 'Month',       DateDiff(Month, @StartDate, @EndDate) Union All
Select 'Quarter',     DateDiff(Quarter, @StartDate, @EndDate) Union All
Select 'Year',        DateDiff(Year, @StartDate, @EndDate)

The two dates are exactly one second apart, so 1000 milliseconds is correct, and so is 1 second, but this is reporting 1 minute, 1 hour, 1 day, 0 weeks, 1 month, 1 quarter, and 1 year. Again, this occurs because of the transitions that occur.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Using datediff the way you did made your query non-sargable.

You may want to take a look at the following blogs to get a better idea of how to write such queries:

microsoft.com/Forums/en-US/transactsql/thread/558c2c58-0087-458d-9199-8b9f1708feec">Indexes Re-build</a>


Only In A Database Can You Get 1000% +

Improvement By Changing A Few Lines Of Code


The ultimate guide to the datetime datatypes
Bad habits to kick : mis-handling date / range queries


PluralSight Learning Library