SQL DateDiff, Between, and GetDate()

Posted by Stephen Weyrick | Tags: sql

Today I was trying to fix problem with a report that sums up hours, and I had a huge brain fart.  What took me an hour to fix, should have only taken me about 5 minutes.  I thought that it would make a good blog post, because I really don't want to make this same stupid mistake again.

The problem involved trying to sum up hours by date, using the built in SQL function getdate().  Below is the query that I was running. 

       SELECT SUM(personnelNumber) as TotalHours, CONVERT(VARCHAR(10), dateTime, 101) as Date
       FROM tblReturnTotals
       WHERE dateTime
       BETWEEN getdate()-7 AND getdate()-1
       AND location = '5'
       GROUP BY CONVERT(VARCHAR(10),  DateTime, 101)

Because getdate() grabs the hour along with the date, the query wouldn't sum the hours from the previous day in the date range.  For instance, if the time is 9 AM right now, then the date range ends yesterday at 9 AM.  Whoops!!!  Below is a quick fix using dateDiff().

       SELECT SUM(personnelNumber) as TotalHours, CONVERT(VARCHAR(10), dateTime, 101) as Date
       FROM tblReturnTotals
       WHERE datediff(dd,datetime, getdate()) < 7 AND
                  datediff(dd,datetime, getdate()) >= 1
       AND location = '5'
       GROUP BY CONVERT(VARCHAR(10),  DateTime, 101)

May 14, 2009

5 responses so far ↓

David - May 14, 2009 at 1:10 PM

Also, don't forget when dealing with dates and MS SQL Server to set the first day of week if you don't want it to be a Sunday:

set datefirst 1;

The above would be a Monday, for example. It's only really an issue if you are dealing with a date sensitive application that takes into account the beginning of a week (for example if you wanted to know calculate the total hours in the current work week).

http://msdn.microsoft.com/en-us/library/aa933164(SQL.80).aspx

Nice post, thanks.

Davo

Stephen Weyrick - May 14, 2009 at 1:19 PM

@David

Thanks for your advice. I seem to always run into little problems with dates that drive me nuts.

Mik - Aug 2, 2009 at 12:09 PM

What would be the best way to ask for all dates in a particular month? Something like this? Yeah... seems to work.

WHERE month(datetime) = #dateFormat(now(),"m")#



keywords: sql server between get month calendar

Stephen Weyrick - Aug 2, 2009 at 2:12 PM

@Mik

That would definitely work. Make sure to be careful if you working in different timezones though. Last week I created a view on some Servers in Tennessee and realized that I couldn't use the getDate() function in SQL, because the timezones of different clients had to match there location.

Mik - Aug 2, 2009 at 5:45 PM

Yup. Got that covered. Thanks.


Oh, and don't forget the year too. Otherwise you'll get everything that ever happened in August.


AND year(datetime) = #dateFormat(now(),"yyyy")#

Leave a Comment