Calculate previous and next months from date string

Question

I'm trying to find the Sales average before and after for selected months over a span of 3 years.

There are two columns, MONTH and SELECTED_MONTH. Both of these are in the same format which is string (eg. 201103). I need to select the particular month in SELECTED_MONTH and get the previous and next month of that month in MONTH.

For example: I have 201103 in SELECTED_MONTH. I need to get the previous and next months in MONTH using that value, so 201102 and 201104.

Any help would be appreciated. I tried different ways which won't work. I use HIVE but even queries in SQL would help.

Answer 1

MySQL offers powerful, but slightly clunky, date arithmetic functions. The functions STR_TO_DATE() and DATE_FORMAT() allow translation from dates represented as text strings to the DBMS's internal DATE and DATETIME formats.

If you happen to have a text column, called, for example, month, in your SQL table, with text dates of the form 201503 (YYYYmm), you can use MySQL date arithmetic to do what you want.

STR_TO_DATE(CONCAT(month,'01'),'%Y%m%d') 

produces the DATE of the first day of the month mentioned.

LAST_DAY(STR_TO_DATE(CONCAT(month,'01'),'%Y%m%d')) 

produces the date of the last day of month.

STR_TO_DATE(CONCAT(month,'01'),'%Y%m%d') - INTERVAL 1 MONTH

produces the DATE of the first day of the preceding month.

STR_TO_DATE(CONCAT(month,'01'),'%Y%m%d') + INTERVAL 1 MONTH

produces the DATE of the first day of the next month.

Carrying this a little further, you can get the YYYYmm format for the first day of the preceding quarter like this.

DATE_FORMAT(STR_TO_DATE(CONCAT('201505','01'),'%Y%m%d') - INTERVAL 1 QUARTER, '%Y%m')

The monkey business of converting between text-string dates and internal dates drives most database designers to store date values internally columns that have the DATE datatype.