2

I have a query which runs and I would like to return records where the date is greater than or equal to a particular date. The column I'm querying against is datetime format.

SELECT COUNT(RSO_ParentID), AssignedDateTime FROM Task WHERE OwnerTeam='2nd Line Support' AND AssignedDateTime>='2015-09-01 00:00:00.000' GROUP BY AssignedDateTime 

Is there a way of having filtering on the date part of AssignedDateTime, and searching relative to the current date (i.e. search for the previous 7 days)?

Thanks, Matt

3
  • 1
    Which dbms product are you using? Too many dbms are far from ANSI SQL compliant when it comes to date/time... Commented Sep 8, 2015 at 12:39
  • Apologies - this is MSSQL. Commented Sep 8, 2015 at 12:41
  • RE: GROUP BY AssignedDateTime Do you want records to ignore the time for purposes of the grouping? Commented Sep 8, 2015 at 17:36

4 Answers 4

3

You could do it like this in MSSQL

SELECT COUNT(RSO_ParentID), AssignedDateTime FROM Task WHERE OwnerTeam='2nd Line Support' AND AssignedDateTime>=DATEADD(D,-7, GETDATE()) GROUP BY AssignedDateTime 
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks Zac. This seems to work - as an additional question, is there a way of returning the results such that it shows "Count of RSO_ParentID" and "Date". At the moment, it returns as date/time so has 339 rows. I'd like each row to be a date...if that makes sense? Sorry it's difficult to explain! :-)
This will do that for you: SELECT COUNT(RSO_ParentID) 'Count of RSO_ParentID', AssignedDateTime 'Date' FROM Task WHERE OwnerTeam='2nd Line Support' AND AssignedDateTime>=DATEADD(D,-7, GETDATE()) GROUP BY AssignedDateTime
Hi Zac, afraid not, I still get each row as it's own date & time: Count of RSO_ParentID Date 1 2015-09-01 14:44:54.000 1 2015-09-01 14:46:23.000 .... I'm guessing I need a way of converting the date/time to just date? I'm using SQL2005 so casting as date isn't an option :(
Oh right, I thought you were just wanting to name the columns, didn't realise you wanted to change the data formatting from the results. Does this help you? You are probably going to want the answer that converts it to a varchar rather than the one marked as being correct on that post. stackoverflow.com/questions/2885400/…
2

Note: This question was not tagged SQL Server 2005 when I answered it. I am leaving the answer, because it is appropriate for SQL Server 2008+.

If you just care about the date and not the time, you need a combination of casting and datediff():

SELECT COUNT(RSO_ParentID), AssignedDateTime FROM Task WHERE OwnerTeam = '2nd Line Support' AND AssignedDateTime >= dateadiff(day, -7, cast(getdate() as date)) GROUP BY AssignedDateTime; 

Note that you can also express this using functions on AssignedDateTime. That is generally a bad idea because it often prevents the use of indexes for the query.

I also am guessing that you want the results by day:

SELECT COUNT(RSO_ParentID), cast(AssignedDateTime as date) FROM Task WHERE OwnerTeam = '2nd Line Support' AND AssignedDateTime >= dateadiff(day, -7, cast(getdate() as date)) GROUP BY cast(AssignedDateTime as date); 

or a total, in which you don't want a group by clause:

SELECT COUNT(RSO_ParentID) FROM Task WHERE OwnerTeam = '2nd Line Support' AND AssignedDateTime >= dateadiff(day, -7, cast(getdate() as date)); 

1 Comment

Thanks Gordon - I receive the error: Msg 243, Level 16, State 1, Line 1 Type date is not a defined system type. when attempting to run - I've corrected the "datediff" typo as well but same error.
0

If you are using MSSQL, use DATEDIFF.

SELECT COUNT(RSO_ParentID), AssignedDateTime FROM Task WHERE OwnerTeam='2nd Line Support' AND DATEDIFF(DAY,AssignedDateTime,GETDATE()) <=7 GROUP BY AssignedDateTime 

1 Comment

This query is not sargable. Check out this article explaining pretty much exactly the query you've written there ;) weblogs.sqlteam.com/dang/archive/2009/03/07/…
0

Try this, it will check only date part, but not time part

SELECT COUNT(RSO_ParentID), AssignedDateTime FROM Task WHERE OwnerTeam='2nd Line Support' AND CAST(AssignedDateTime AS DATE) >= DATEADD(D,-7, GETDATE()) GROUP BY AssignedDateTime 

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.