Thursday, 7 October 2010

SQL Tip - Getting last week start and end date in SQL Server Query

Here i am going to talk a little about SQL Server Dates. Its a bit annoying when you want to play with dates in SQL query.
 Its easy to Add date or find difference between two dates but if you want to find start and end date of any week. Here i will give example of last week but it can be any week's start and end Date. I will define everything in steps.

1. First find the week we want start and end date for.
                     DATEADD(DAY,-7,getDate())

2. Then we find difference between that day and week.
                     DATEDIFF(wk ,0, DATEADD(DAY, -7, getDate()))

3. Then we will use date add to find the first day of the week.
                     DATEADD(wk, DATEDIFF(wk, 0, DATEADD(DAY, -7, GETDATE())) ,0)   

Above will give us start date object for previous week. To get the end date of a week we can use query, here again as an example i will use last week.


DATEADD(ms, -10, DATEADD(wk, DATEDIFF(wk, 0, DATEADD(DAY, -7, GETDATE())) ,7))