Alex_404 Alex_404 - 3 years ago 162
SQL Question

Alternating values based on current date

I need to create a SELECT statement with a column which alternates between two values from day to day. Values are 1 and 2.
I was thinking about:

select datepart(d,getdate())%2

But this does not work in transition from 31st to 1st day of next month.

I could create some advanced SQL with case statements to check previous month etc, but this syntax should be as easy as possible since this will be exposed as self service to users.

Any ideas?

Answer Source

Use datediff():

select datediff(day, '2000-01-01', getdate()) %2
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download