query to fetch records for alternate days based on a column

Question

I have a table altstore with a column checkindate which keeps the date. My requirement is to fetch records of alternate days for a month for example of day 1st of the month, then 3rd and so on. Please help me with the sql query which I can use. Thanks.

Answer 1

Try using:

select * from altstore
where DATEDIFF (day,startingdate,enddate) % 2 is 1

here startingdate is first date of the month and enddate is value in column checkindate.

Answer 2

Use datepart and modulo:

select
    *
from
    table
where
    datepart(dd, checkindate) % 2 = 1
    and checkindate between '2011-12-01' and '2011-12-31'