Here is an interesting question I received from my friend who is working in Bank as a DBA.
“Pinal,
We have a requirement in bank that every 2nd and 4th week we keep more cash in our bank where as we can keep less cash on other weeks. I want to write an automated script which indicates that if the current week is ODD or EVEN. Based on this information, I can write more actions in my procedures. Do you have such script which can help me?”
Very interesting question. The matter of fact, I have a script which I have been using quite a while for similar logic. The script is not written by me, but I have it with me as a resource for quite a while. Here is the script.
DECLARE @CurDate DATETIME
SET @CurDate = GETDATE()
SELECT
WeekOfMoth = DATEPART(wk, @CurDate)
- DATEPART(wk,DATEADD(m, DATEDIFF(M, 0, @CurDate), 0)) + 1,
CASE WHEN (DATEPART(wk, @CurDate)
- DATEPART(wk,DATEADD(m, DATEDIFF(M, 0, @CurDate), 0)) + 1) % 2 = 1
THEN 'Odd' ELSE 'Even' END EvenOrOdd
If I run above script for today’s date 12/7/2014, it will give me following results.
Reference: Pinal Dave (https://blog.sqlauthority.com)
7 Comments. Leave new
Hi,
I didn’t get why you first find first day of month then proceeding to find even or odd.I think only finding week of that date will do. Like,
DECLARE @CurDate DATETIME
SET @CurDate = GETDATE()
SELECT CASE WHEN (DATEPART(wk, @CurDate)% 2) = 1
THEN ‘Odd’ ELSE ‘Even’ END EvenOrOdd
First day of month required for calculation to check weekday, as we are not checking week no. of year, we need to check week no. of month.
DATEPART(wk, @CurDate) will return week no. of year
For example check for 1st Nov. 2014 or 1st Feb. 2015
Hello Pinal,
Love your blog, I read it often!
Using a modulo operator you can get the answer in a lot less code like this:
select case when datepart(wk, getdate()) % 2 = 0 then ‘Even’
else ‘Odd’
end
For 7 Dec 2014, It is returning result “Even”
actually, it is considering first day of the week .
I know this is a bit old but I find it really interesting.
There is not enough information in the request though. i.e. when he says 2nd and 4th, is he talking about the 2nd week of the month, the year or what? If I take it that what he is actually asking for is every other week the bank keeps more cash, then the task becomes simple i.e. every other week since the beginning of time (which in most systems is 1/1/1900).
Therefore:
CONVERT(INT,GETDATE()/7) % 2
will do the job, 0 = EVEN, 1 = ODD changing every Monday (because 1/1/1900 was a Monday)
Where “Convert(INT(GETDATE()” – returns number of days since 1/1/1900, “/7” divides it into weeks, %2 says whether the result is divisible by 2.
This also avoids the 53 week issue where you get 2 consecutive ODD weeks (53 and 1). I use this method for backup scheduling.