Reputation: 1088
I need to get the first date of month of any date using Spark SQL.
I have date column in my table
Upvotes: 8
Views: 31274
Reputation: 12585
You can use this function TRUNC()
TRUNC( Field1 , 'MM' )
For exemple: TRUNC( '2022-04-05' , 'MM' )
Result is : 2022-04-01
Upvotes: 6
Reputation: 2370
Here is a great way to do convert date/timstamp to first day of the month
> to_date(date_trunc('MM', '2015-03-05T09:32:05.359')) AS mm
DD = 2015-03-01 = (TYPE: DATE)
Bonus Round:
/**************
Date Functions
* https://learn.microsoft.com/en-us/azure/databricks/spark/latest/spark-sql/language-manual/sql-ref-functions-builtin#date-and-timestamp-functions
* https://docs.databricks.com/sql/language-manual/functions/date_trunc.html
**************/
SELECT
CURRENT_DATE()
, CURRENT_TIMESTAMP()
, date_format(CURRENT_TIMESTAMP(),'yyyy-mm-dd') AS fmt_date_expl_1
, date_format(CURRENT_TIMESTAMP(),'yyyy') AS fmt_date_expl_2
, to_date(date_trunc('YEAR', CURRENT_TIMESTAMP())) AS `year`
, to_date(date_trunc('MM', CURRENT_TIMESTAMP())) AS `mm`
, to_date(date_trunc('DD', CURRENT_TIMESTAMP())) AS `dd`
, date_trunc('HOUR', CURRENT_TIMESTAMP()) AS `hour`
, date_trunc('MILLISECOND', '2015-03-05T09:32:05.123456') AS `millisecond`
/*****
current_date()|current_timestamp()|fmt_date_expl_1|fmt_date_expl_2|year |mm |dd |hour |millisecond |
--------------+-------------------+---------------+---------------+----------+----------+----------+-------------------+-------------------+
2021-09-29|2021-09-29 16:07:52|2021-07-29 |2021 |2021-01-01|2021-09-01|2021-09-29|2021-09-29 16:00:00|2015-03-05 09:32:05|
-----------
Name # Type Catalog Name
current_date() 0 DATE Spark
current_timestamp() 1 TIMESTAMP Spark
fmt_date_expl_1 2 STRING Spark
fmt_date_expl_2 3 STRING Spark
year 4 DATE Spark
mm 5 DATE Spark
dd 6 DATE Spark
hour 7 TIMESTAMP Spark
millisecond 8 TIMESTAMP Spark
-------------
******/
Upvotes: 0
Reputation: 11
Spark sql methods
date_add(last_day(ADD_MONTHS(CAST(CURRENT_TIMESTAMP AS DATE),-2)), +1) --First day of previous month
last_day(ADD_MONTHS(CAST(CURRENT_TIMESTAMP AS DATE),-1)) ---Last day of previous month
Upvotes: 1
Reputation: 12585
You can do that :
%sql
SELECT
Date("2019-01-18"),
date_add(last_day(add_months(Date("2019-01-18"), -1 )),1) First_Date_Of_Month
Instead of Hard Coded Date you can use your date column.
Upvotes: 2