Go Premium for a chance to win a PS4. Enter to Win

x
?

Find the month to which date belongs to

Posted on 2017-03-28
4
Medium Priority
?
54 Views
Last Modified: 2017-07-31
I am trying to implement a solution to the problem using SQL (DB2).

A month is defined based on below condition :
If 1st of month is on a Wednesday or earlier, then that corresponding Monday marks the beginning of the month
Say : Mar 1 2017 was a Wednesday so the month 201703  will start from 28th Feb.
If 1st of month is on a Thursday or later, then the next Monday marks the beginning of the month.
Say :  Apr 1 2017 is a Saturday so the month 201704 will start from  3rd April.

Now based on the logic,  I need to update the month for each and every date in a table. The date could be of any value. Accordingly to above month definition, I should update the month value.

Say 28-03-2017 should belong to 201703 , 01-04-2017 should still belong to 201703 etc.

could someone advise.

thanks
0
Comment
Question by:Gayathri R
  • 2
4 Comments
 
LVL 18

Expert Comment

by:Dave Ford
ID: 42068387
We'll start with the formula to get the "day of the week" of the first day of the month.

select dayofweek(last_day(current date - 1 month) + 1 day)
  from sysibm.sysdummy1

DAYOFWEEK
        4

Open in new window


HTH,
DaveSlash
0
 
LVL 18

Expert Comment

by:Dave Ford
ID: 42068388
You'd wrap that expression in a CASE statement to build your final result.
0
 

Author Comment

by:Gayathri R
ID: 42069330
Hi Daveslash,

Thanks for your inputs about finding day of week for the first day of the month. But in the above problem - after finding out a logic , I think dates 27 to 31 have to be treated differently and  dates 1 to 4 have to treated differently as the month they belong to will change in each case.
 I believe it can be  put up in 2 cases like this. But it involves additional overhead of checking what the date is first and the apply logic.
Any feedback on this most welcome.

--IF DATE>=27 AND <= 31  DATE MARCH 27 -> TAKE +1 MONTH
SELECT CASE WHEN DAYOFWEEK_ISO(date('2017-03-01') + 1 MONTH ) <= 3
            THEN
----------------------------------------- + 1 MONTH
       INT(STRIP(CHAR(YEAR('2017-03-01'))) CONCAT
       SUBSTR(DIGITS(MONTH('2017-03-01') + 1),9,2))
            ELSE
----------------------------------------- + 0 MONTH
       INT(STRIP(CHAR(YEAR('2017-03-01'))) CONCAT
       SUBSTR(DIGITS(MONTH('2017-03-01')    ),9,2))
       END AS FMONTH
FROM  <<some table>>

--IF DATE >=1 AND <=4  DATE APR 3 -> TAKE SAME MONTH
SELECT CASE WHEN DAYOFWEEK_ISO(date('2017-04-01') ) <= 3
            THEN
----------------------------------------- + 0 MONTH
       INT(STRIP(CHAR(YEAR('2017-04-01'))) CONCAT
       SUBSTR(DIGITS(MONTH('2017-04-01') ),9,2))
            ELSE
----------------------------------------- - 1 MONTH
       INT(STRIP(CHAR(YEAR('2017-04-01'))) CONCAT
       SUBSTR(DIGITS(MONTH('2017-04-01') -1   ),9,2))
       END AS FMONTH
FROM  <<some table>>  

Thanks
Gayathri
0
 
LVL 27

Expert Comment

by:tliotta
ID: 42074170
What DB2 platform and version are you working with? An older version might need more messy code than some recent versions.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question