There are plenty of built-in functions available in Apache Impala that helps in analyzing the data and business logic implementations. It relieves a developer’s worries and eliminates the necessity for user-defined functions. Built-in functions aid analysts in the process of analyzing, cleansing, transforming, and modeling data in order to identify valuable information, draw conclusions, and support decision-making.
This is one of a series of articles describing how to use “date-time” functions. We’ll go through how to add and subtract a certain number of months from a given date, in this post.
MONTHS_ADD and MONTHS_SUB are the functions that adds and subtracts a specified number of months from a TIMESTAMP value. These functions deal with an actual TIMESTAMP value instead of a string that is converted to a TIMESTAMP.
Syntax:
MONTHS_ADD(TIMESTAMP startdate, INT days),
MONTHS_ADD(TIMESTAMP startdate, BIGINT days)
MONTHS_SUB(TIMESTAMP startdate, INT days),
MONTHS_SUB(TIMESTAMP startdate, BIGINT days)
Examples:
SELECT NOW() AS CurrentDate, MONTHS_ADD(NOW(), 1) DateAfter1Month;
….—…………CurrentDate…………….|………..–.DateAfter1Month–………..
2021-11-01 14:00:11.907030000 | 2021-12-01 14:00:11.907030000
SELECT NOW() AS CurrentDate, MONTHS_SUB(NOW(), 1) DateBefore1Month;
….—…………CurrentDate…………….|………..–.DateBefore1Month–………..
2021-11-01 14:01:09.211529000 | 2021-10-01 14:01:09.211529000
If you wish to have only date, then you can use TRUNC function to truncate ‘timestamp’ from the date, as shown below.
SELECT TRUNC(NOW(),’dd’) DtNow, MONTHS_ADD(TRUNCT(NOW(),’dd’),1) Dt1MonthLater;
…………….DtNow……..|……..Dt1MonthLater……
2021-11-01 00:00:00 | 2021-12-01 00:00:00
I hope you found this post to be informative.
Please join our mailing list to receive more interesting information.
Big Data and SQL 