CODEWITHSUNDEEP
sqlt-sql
b0redj0rd
b0redj0rd

Reputation: 53

SQL: Datepart searches

I am hoping someone wouldn't mind helping with something that I am stuck on.

I need to produce a report which gives a count of items which have been created over a certain date range. For example, when a user would run this report on the 15/09/2013 it would show a count of any records which has a creation date of the 1st of the current month, or before.

The code I've tried to get working is below, but doesn't run. I wonder if I'd be better using datepart for this? 

SELECT RECORDTYPE, COUNT(RECORDTYPE) AS 'COUNT OF RECORDS' 
FROM VIEW_GLOBALSEARCH 
WHERE DATEPART(YYYY,CREATIONDATE) = DATEPART(YYYY, GETDATE ()) 
  AND DATEPART(MM,CREATIONDATE) = DATEPART(MM, GETDATE ())
  AND DATEPART(DD,CREATIONDATE)= '01'
   OR CREATIONDATE <= DATEPART(MM, CREATIONDATE) = DATEPART(MM, GETDATE())
DATEPART(YYYY,CREATIONDATE) = DATEPART(YYYY, GETDATE ())

Any advice is appreciated - Sorry if this is basic.

Upvotes: 0

Views: 162

Answers (3)

HeimerTown
HeimerTown

Reputation: 55

This will get all records up through 8/31.


    SELECT RECORDTYPE, COUNT(RECORDTYPE) AS 'COUNT OF RECORDS' 
      FROM VIEW_GLOBALSEARCH
     WHERE Year(CREATOINDATE)

Upvotes: 0

Declan_K
Declan_K

Reputation: 6826

For "a creation date of the 1st of the current month, or before." use the following WHERE clause. The second piece of this will always returnt he first day of the current month.

WHERE CREATIONDATE <= DATEADD(m, DATEDIFF(m, 0, getdate()), 0)

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

You are better off not using datepart. Functions in the where clause slow down performance. A better approach is to use this approach.

where creationdate >= the start of your date range
and creationdate < the day after your date range

Not only will this run faster, it will take the time component of creationdate into account.

Upvotes: 0

Related Questions