Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?

Find the month to which date belongs to

Posted on 2017-03-28
4
Medium Priority
?
36 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Join & Write a Comment

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

730 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