Monday, March 30, 2009

T-SQL: friday of the current week

This was a question asked to me in order to return the last day of the current week.

The main idea is to schedule a package update for Friday being the day of less Data Warehouse use.

The response is in this link, on an MSN forum, since I only changed another response:

http://social.technet.microsoft.com/Forums/en-US/sqlintegrationservices/thread/4613e458-13a1-441e-b1b5-d5bb81af6639/


The presented solution works in either T-SQL and SSIS (data converter tool) there can be other variations and there can be some that only work in one case like:

SSIS:

DATEADD("WEEK",DATEDIFF("WEEK",(DT_DATE)("01/01/1900"),GETDATE()),(DT_DATE)("05/01/1900"))

T-SQL:
select DATEADD("WEEK",DATEDIFF("WEEK",'01-01-1900',GETDATE()),'01-05-1900')


and a more complex way, by counting the week until now and then returning the last day of current week:

T-SQL & SSIS:
SELECT DATEADD(week,DATEDIFF(week,'01-01-1900',GETDATE()),'01-05-1900')

Any questions, explanations, just comment.

No comments: