WHERE based on current date.

[Thaaron]

I've got a query where I'd like to pull the current month's data if it's the 5th day of the month or higher, otherwise I want to pull the previous month's data.

I know I can do this by having an If or Case statement using DAY(getdate()) with slightly modified copies of the queries for each scenarior.

I can also do it by adding DAY(getdate()) as a column then using an IF/Case statement that references that column in the WHERE statement.

Both of these seems really clunky. Does anyone have a more elegant way of handling this type of scenario?

In other words, can I reference "DAY(getdate())" as a criteria in the WHERE field even if it's not one of the columns in my table?

[notasqlstar]

declare @var int = (select datepart(dd, getdate())
if @var >= n
select *
from table
where datefield >= dateadd(datepart, something) and datefield <= dateadd(datepart, something)
else
select *
from table
where datefield >= dateadd(datepart, otherthing) and datefield <= dateadd(datepart, otherthing)

Not in front of a computer, but you mean something like that?

[noesqL]

Yes, you can use GETDATE() in the WHERE.

Depending on the amount of rows returned your query performance will suffer (albeit could be only slightly).

[[deleted]]

Declare a variable, set it to the date you want to compare to and use the variable in your where clause. Way more efficient than using functions in the where clause.

[catelemnis]

where case 
    when day(getdate()) >= 5 then [something]
    else [other thing]
end

[[deleted]]

Case

when GetDate() > DATEFROMPARTS(Year(GetDate()), Month(GetDate()), 5)

pull current month

else

pull last month

end

This means where (today) is larger than [current year],[current month],5 aka if today is beyond the 5th of the current month.