sql - Ignore records between last thursday and this thursday not working -
i working on stored proceedure user passes in payment type simple web application return payment types.
for credit card payments want ignore records fall between thursday of last week , thursday of week.
i have written this:
select dateadd(day, (datediff (day, 4, getdate()) / 7) * 7, 3) select convert(datetime, convert(nvarchar(20), dateadd(day, 4 - datepart(dw, getdate()), getdate()), 103)) which finds out thursday of last week , thursday of week.
however when use in stored proc still returns records between dates.
this section of stored proc used
else if @paymenttype = 'credit card' begin select distinct [variable] ,[variable] ,[variable] ,[variable] ,[duedate] ,[variable] ,[variable] ,[variable] ,[variable] ,[variable] ,[variable] ,[variable] mytable paymentmethod = 'credit card' , [duedate] not between dateadd(day, (datediff (day, 4, getdate()) / 7) * 7, 3) , convert(datetime, convert(nvarchar(20), dateadd(day, 4 - datepart(dw, getdate()), getdate()), 103)) order duedate desc end this screenshot of of data (cant show more due data protection) 
for way want work shouldn't returning these records (they wednesday of current week want ignore)
i cant figure out why returning these appriciated
just update, after tinkering convert part creating issue removed , works
where paymentmethod = 'credit card' , [duedate] not between dateadd(day, (datediff (day, 4, getdate()) / 7) * 7, 3) , dateadd(day, 4 - datepart(dw, getdate()), getdate()) order duedate desc not sure why put convert part in there
Comments
Post a Comment