0

How do I derive the first day of the last quarter pertaining to any given date in Spark-SQL query using the SQL API ? Few required samples are as below:

input_date | start_date
------------------------
2020-01-21 | 2019-10-01
2020-02-06 | 2019-10-01
2020-04-15 | 2020-01-01
2020-07-10 | 2020-04-01
2020-10-20 | 2020-07-01
2021-02-04 | 2020-10-01

The Quarters generally are:

1 | Jan - Mar
2 | Apr - Jun
3 | Jul - Sep
4 | Oct - Dec

Note:I am using Spark SQL v2.4.

Any help is appreciated. Thanks.

dexter80
  • 75
  • 2
  • 12

2 Answers2

2

Use the date_trunc with the negation of 3 months.

df.withColumn("start_date", to_date(date_trunc("quarter", expr("input_date - interval 3 months"))))
  .show()

+----------+----------+
|input_date|start_date|
+----------+----------+
|2020-01-21|2019-10-01|
|2020-02-06|2019-10-01|
|2020-04-15|2020-01-01|
|2020-07-10|2020-04-01|
|2020-10-20|2020-07-01|
|2021-02-04|2020-10-01|
+----------+----------+
Lamanus
  • 12,898
  • 4
  • 21
  • 47
0

Personally I would create a table with the dates in from now for the next twenty years using excel or something and just reference that table.

Ed Elliott
  • 6,666
  • 17
  • 32
  • isn't it possible by any other way ? For. e.g. date_trunc('quarter'...) etc to find the first month of the last quarter and then concat '01' at the end to specify the first day ? – dexter80 Sep 09 '20 at 15:25
  • Probably, I’ve done this in about a dozen different systems over the years and this never fails. – Ed Elliott Sep 09 '20 at 16:58