I was building a query that ranked users by the number of votes they cast per day, and it dawned on my that doing the following has a serious flaw (which may well be my fault?):

/* with added line break to avoid horizontal scroll */

ROUND(CAST((U.UpVotes + U.DownVotes) AS FLOAT) 
/ DateDiff(day,CreationDate,getdate()),3) AS VotePerDay

The DateDiff is counting the days from the users creation to the date of the query, but the vote counts are from the users creation to whenever the data explorer was last updated.

So, is there are way to get the date/time of the last data update with a query?
Preferably this done in a manner that can just be dropped in to replace getdate().
And I do not wish to get the user to enter a parameter value.


As a workaround, you could get the date of the most recent vote, which is likely to be very close to the date of the last update, at least on the more popular sites.

select top 1 CreationDate from Votes order by CreationDate desc​
| improve this answer | |
  • This works (see the query). My final version uses DECLARE @lastDataUpdate DATETIME , SELECT @lastDataUpdate = MAX(CreationDate) FROM Posts and then uses @lastDataUpdate in place of getdate() (however, this also requires adding 1 to the DateDiff value to avoid a divide by zero, but that's better than an arbitrarily changing offset). – DMA57361 Feb 22 '11 at 21:13

I've created the query latest-date-with-posts to find the last date with data.

It can be used in other queries

DECLARE @EndDateExcluded DateTime

Set @EndDateExcluded = (
    SELECT top 1 cast(Posts.CreationDate as date) AS Date
    FROM Posts ORDER BY Posts.CreationDate des

See example in http://data.stackexchange.com/programmers/query/119275/reputation-vs-new-reputation-only-for-last-month

| improve this answer | |

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .