July 24, 2005 at 9:31 pm
OK, I'm still getting caught up, so I went with this tip from a reader:
I enjoy reading the tips submitted by others, and some of them have saved me lots of work!
I would like to submit the following tip as a very small "thank you" to the sqlserver central team and all its contributors out there.
Regards,
Maria
Using T-SQL to find the Monday of the current week
Have you ever needed to find the date for a specific day of the week?
At my place of work we need to 'join' on spreadsheets that are created semi-automatically and usually on a Monday. The file names are like this: somepath\yyyymm\filename yyyymmdd.xls. When we process the files sometime later in the week I find it convenient use T-SQL to construct the filenames on the fly.
This is not as straightforward as you might think! The first day of the week is determined by the value of @@DATEFIRST, so for example Monday may or may not be the first day of the week. Below is a formula I have been using for a while now, to find the Monday of the current week. Maybe others find it useful too:
SELECT GETDATE() + (1 - (DATEPART(dw, GETDATE())) + (1 - @@DATEFIRST)) % 7
Explanation:
(1 - DATEPART(dw, GETDATE())
Step back to @@DATEFIRST, by using the relative day of the week. The 1 is because dw is 1-based
(1 - @@DATEFIRST)
Step back to a Monday. @@DATEFIRST = 1,2,3 for Sun, Mon, Tue... Again the 1 is because @@DATEFIRST is 1-based
% 7
Do not add/subtract more than 7
Doing this sort of thing would be much easier with an ActiveX task in DTS, but unfortunately for various reasons this is not appropriate for us - yet!
July 26, 2005 at 2:23 pm
I just look for the spreadsheet I hate the most.
I hate mondays.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply