CODEWITHSUNDEEP
sql-serversql-server-2005
Matt
Matt

Reputation: 75

Convert Date/Time to Date

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

Upvotes: 2

Views: 124

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

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));

Upvotes: 2

Lali
Lali

Reputation: 2866

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

Upvotes: 0

ram hemasri
ram hemasri

Reputation: 1634

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

Upvotes: 0

Zac Braddy
Zac Braddy

Reputation: 611

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

Upvotes: 3

Related Questions