CODEWITHSUNDEEP
sqlsql-server
Roger Steinberg
Roger Steinberg

Reputation: 1604

Get query results for all date values besides current month

I need to get values for data excluding current month and taking this year into account.

What I currently have is the following 

WHERE 1=1
    AND CAST(created_at AS DATE) >= '2018-01-01'
    AND MONTH(CAST(created_at AS DATE)) != MONTH(GETDATE())

Obviously this will also exclude 2018 current month data as well which I want to prevent.

I've checked different solutions online but failed to apply it.

Upvotes: 0

Views: 2446

Answers (2)

MladenB
MladenB

Reputation: 161

The correct WHERE clause related on your question will be

   WHERE created_at BETWEEN '2018-01-01' AND (DATEADD(dd, -1 ,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)))

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I would simply do:

where created_at < dateadd(day, 1 - day(getdate()), cast(getdate as date))

This is also sargable -- a mouthful that means that an index can be used for the query.

You can also write this as:

where created_at < datefromparts(year(getdate()), month(getdate()), 1)

This is actually better and clearer.

Upvotes: 3

Related Questions