Recently my colleague challenged me to find a method for identifying the week ending date for the week which any given date belongs to; i.e. say for a gven date which happens to be a Tuesday, what is the date of the end of the week? In this example, week ending is defined as a Sunday, however depending on how your server is set up, week ending may be considered the Saturday (or more accurately week commencing may either be a Sunday or a Monday), but we easily deal with this as you will see.
T-SQL
The two methods are actually quite different as Oracle has some handy functions to help us, which T-SQL does not, therefore this method simply involves a calculation based on knowing which day of the week a given day is, and how many days are in a week, therefore how many days until the end of the week.
select cast(CONVERT(char(10), getdate(), 110) as datetime) [today]
, datename(dw, getdate()) [day]
, datepart(dw, getdate()-1) [day of week]
, datepart(dw, getdate()-1)-1 [days since monday]
, 7-datepart(dw, getdate()-1) [days until sunday]
, cast(CONVERT(char(10), (getdate()-(datepart(dw, getdate()-1)-1)), 110) as datetime) [monday]
, cast(CONVERT(char(10), (getdate()+(7-datepart(dw, getdate()-1))), 110) as datetime) [sunday]
The first line simply identifies the date in question, which for this example is the current date, cast to remove the time portion, then we can use the datepart and datename functions to identify further details about this date.
After identifying the name of the day, and day of the week (note that my server is configured to take Sunday as the first day of the week, but I want it to be Monday in this scenario, therefore I subtract 1 to make Monday the first day) it is relatively straightforward to calculate how many days since Monday and until Sunday. Days since is defined as 'day of week'-1, because Monday is the first day of the week; Days until is defined as 7-'day of week', because Sunday is the seventh day of the week.
Using these two numbers we can then either subtract or add them from the given date, which is then cast as a date to given the dates of the beginning and end of the week in question.
PL/SQL
In Oracle the task is much simpler because we can make use of the next_day function to identify the date of a subsequent weekday for a given date.
select trunc(sysdate) "Today"
, next_day(trunc(sysdate-7), 'monday') "Monday"
, next_day(trunc(sysdate), 'sunday') "Sunday"
from dual
;
This makes identifying the next Sunday very easy as can be seen, and whilst for some reason there isn't a corresponding 'previous day' function, by subtracting 7 from our date and then finding the next Monday, we can find the date of the previous Monday without too much trouble.
Now that we have these methods, they can be incorporated into my previous entries for date range lookups for bothe PL/SQL and T-SQL
James