hi All,
i need to get the summary report from DB between last sunday to till date where my run is from Sunday to Friday.
so if i run on tuesday i shuld get the data from last sun to Tue,
if i run on fri it shoudl return from last sun to Fri
if i run on sunday it should return only sunday's data
WHERE table_date >= DATEADD(DAY, -DATEDIFF(DAY, 6, GETDATE()) % 7, CAST(GETDATE() AS date))
AND table_date < DATEADD(DAY, -DATEDIFF(DAY, 6, GETDATE()) % 7 + 7, CAST(GETDATE() AS date))If what you say above is true and if you don't have "future" data that goes beyond the instant you run it and you have no previous dates less that 31 Dec 1899, then we can seriously simplify the problem with the following WHERE...
WHERE SomeDateColumn >= DATEADD(dd,DATEDIFF(dd,-1,GETDATE())/7*7,-1)
That works because -1 is the "date serial number" for the Sunday that occurs on 31 Dec 1899. It counts the number of days from then 'til now and then does the integer math (which strips the time off, as well) of calculating whole weeks by dividing by 7 and then converts those back to days by multiplying by 7 and converting the resulting number of days back to a DATETIME, which will always be the Sunday that last occurred prior to right now.
If you do have future dates and need to limit the end date, then it's only a bit more complex and similar to what Scott has already provided...
WHERE SomeDateColumn >= DATEADD(dd,DATEDIFF(dd,-1,GETDATE())/7*7,-1)
AND SomeDateColumn < DATEADD(dd,DATEDIFF(dd,0,GETDATE())+1,0)
... which could also be simplified a bit...
WHERE SomeDateColumn >= DATEADD(dd,DATEDIFF(dd,-1,GETDATE())/7*7,-1)
AND SomeDateColumn < CONVERT(DATE,GETDATE()+1)
If you believe in the myth of portability, then you would need to use something not proprietary to T-SQL, like the following...
WHERE SomeDateColumn >= DATEADD(dd,DATEDIFF(dd,'18991231',SYSDATETIME())/7*7,'18991231')
AND SomeDateColumn < CAST(DATEADD(dd,1,SYSDATETIME()) AS DATE)
I'm never going to assume past dates could not be run, so I would never use just:
< CONVERT(DATE,GETDATE()+1)
Thus, my actual prod code would not reference "GETDATE()", but @date, which would be a parameter that defaults to current date if not passed in, but which could be passed in to run any past date if needed.
I also don't use negative dates as "tricks" to knock a byte or two out of coding. I stick to a standard pattern for date calcs.
I always agonize over this. The tricks make the code look so compact and even more readable, and it has never broken. Yet I feel like I am doing something dirty or sneaky when I use those tricks. The end result is that in my code I have the worst of both worlds. When I don't want to feel sneaky, I avoid the tricks. When I am not thinking about dirt or being sneaky, I end up using the tricks.
No problem. They are very well recognized shortcuts, though.
Hmm. If so, then why did you need to add these comments: "That works because -1 is the "date serial number" for the Sunday that occurs on 31 Dec 1899. ..."
How much code does trick really "save" you? A few bytes, perhaps? Is that worth the possible confusion and making it more difficult to copy the code for simple reuse?
If you Google "sql server date 0", you can find that it's Jan 01, 1900 without even clicking into any link. Google "sql server date -1" and it's basically nuttin' honey, just like the cereal.
Heh... Because I knew you wouldn't know. 
Just kidding... most newbies don't know and so I included it for the benefit of the OP. They also wouldn't understand it if you listed the actual date because they're not used to such date math.
Understood about the SMALLDATETIME thing but you also used a DATE conversion. Heaven forbid that because there might be someone that still has SQL Server 2005. 
If the OP is actually using the mistake known as SMALLDATETIME (or, in the case of your code, 2005), they'll be back and we can figure something else out.
Is this trick really worth it, though, i.e. do you save that much time/code using it? Newbies won't know it, and they won't be able to find it. Date 0 in SQL is used so often it's easy to Google it and find its meaning. Date 6 seems easy enough based on understanding date 0, any negative value less so, at least to me.
We do have some tables that still have a smalldatetime legacy, just because no one can afford the time to confirm that changing it to "date" will definitely not cause a problem in any code, any where.
If you have
Thank you