1

I would like to set a date dynamically in the where clause of my query but I can't figure out how to do it.

The query is doing a count of the number of rows with an entered on date >= 1st August year X.

The dynamic part is how the year is calculated.

If the query is run before 1st January the entered on date in the where clause will be currentyear/08/01.

If the query is run from 1st January to 31st July the entered on date in the where clause will be previousyear/08/01.

Does anyone know how this can be done?

  • 1
    As an aside, this is why many people have a date or calendar table with every day of the year in it. They then put in additional fields such as fiscal year, fiscal month, etc. This makes it easy to join on this table and then siimply apply the filter of which fiscal year/month range you want. – Jonathan Fite Jul 23 at 13:07
2

You don't need dynamic SQL for that. Something like this should work:

WHERE date_column >= DATEFROMPARTS( 
        YEAR(GETDATE()) - CASE WHEN MONTH(GETDATE()) >= 8 THEN 0 ELSE 1 END, 
        8, 1 )
2

Try using CASE expressions as you can specify qualifiers like what you're describing.
something like...

Code :

Select Count (*)
CASE when date >= 1st August year X THEN enteredondate = currentyear/08/01
ELSE enteredondate= previousyear/08/01
End
from DateTable

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.