How to get every month 20 date from a table

[Rohullah]

Dear All,

I want to create a view and the view only should get those rows from a table which a dateColumn has in this format(MM/DD/YYYY) this date(XX/20/XXXX) date for every month if the date is 21 in the view should not show that record, means for only one day the view should hold/keep the record.


thanks in advance.

[Luuk]

my dateformat is se differntly, bu that should not make a difference, because the DATE is stored, and not a string representation of that date.

create table testDate (d date);

insert into testDate values('2014-01-01');
insert into testDate values('2014-01-19');
insert into testDate values('2014-01-20');
insert into testDate values('2014-01-21');

select * from testDate where day(d)=20;

above will only return 1 row.

[Rohullah]

Dear Luuk,
i want to select all those record from a table whose 20 days are completed in the system, means ofter every 20 days the view should select those rows from the table whose 20 days are completed,

I have done as bellow but i am not sure its okay or not.

declare @Date tinyint
set @Date=day(getdate())
select @Date,* from BillBoards 
where day(@Date)-day(Billaboards.BillBoardDate)=20;

Thanks And Records

[Rabbit]

Please use code tags when posting code or formatted data.

I have no idea what you are trying to do. Your explanations are confusing. Please post some sample data and results.

[Luuk]

yes, indeed confusing is what he wants... (free after Yoda ;)

But, i think, you might want to do this?:

SELECT * from BullBoards
WHERE Billaboards.BillBoardDate < DATEADD(dd,-20,getdate())