SQL SERVER – Finding if Current Week is Odd or Even – Script

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.

SQL SERVER - Finding if Current Week is Odd or Even - Script weekofmonth

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Reverse String Word By Word
Next Post
SQL SERVER -Fix Error – Cannot open backup device. Operating system error 5(Access is denied.)

Related Posts

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

    Reply
    • 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

      Reply
  • Tristan Geraets
    December 7, 2014 4:01 pm

    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

    Reply
  • For 7 Dec 2014, It is returning result “Even”

    Reply
  • 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.

    Reply

Leave a Reply

Menu