Spark SQL - Add Day, Month and Year to Date

visibility 6,797 event 2022-06-14 access_time 2 years ago language English

thumb_up 0

more_vert

Spark SQL provides quite a few functions we can use to add days, months or years to a date or timestamp. These functions include date_add, date_sub, add_months, etc. These functions can also be used to subtract days, months or years from a date.

The following are the examples.

Add days

spark-sql> select date_add(DATE'2022-06-01', 1);
2022-06-02

Subtract days

spark-sql> select date_add(DATE'2022-06-01', -1);
2022-05-31
spark-sql> select date_sub(DATE'2022-06-01', 2);
2022-05-30

Add months

spark-sql> select add_months(DATE'2022-06-01',1);
2022-07-01

Substract months

spark-sql> select add_months(DATE'2022-06-01',-2);
2022-04-01

Add years

There is no direct function we can use but we can use add_months. For example, adding one year is equivalent to add 12 months.

spark-sql> select add_months(DATE'2022-06-01',12) as add_one_year, add_months(DATE'2022-06-01',24) as add_two_years;
2023-06-01      2024-06-01

Subtract years

spark-sql> select add_months(DATE'2022-06-01',-12);
2021-06-01

Log in with external accounts

toc Table of contents

Add days
Subtract days
Add months
Substract months
Add years
Subtract years

insights Stats

warning Please login first to view stats information.

tag Spark SQL Functions

article Spark SQL - Convert String to Timestamp article Spark SQL - Left and Right Padding (lpad and rpad) Functions article Spark SQL - NTILE Window Function article Spark SQL - Convert Date/Timestamp to String via date_format Function article Spark SQL - Check if String Contains a String Read more (46)

tagSpark SQL