CODEWITHSUNDEEP
apache-spark-sql
Rajiv Singh
Rajiv Singh

Reputation: 1088

How to get First date of month in Spark SQL?

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

Answers (6)

Ardalan Shahgholi
Ardalan Shahgholi

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

SherlockSpreadsheets
SherlockSpreadsheets

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

androidlearner
androidlearner

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

Ardalan Shahgholi
Ardalan Shahgholi

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

Willem
Willem

Reputation: 1124

In pyspark (>= v1.5) you can use the trunc function:

import pyspark.sql.functions as sf

df.withColumn('first_of_month', sf.trunc('date_col', 'month'))

Upvotes: 3

Rajiv Singh
Rajiv Singh

Reputation: 1088

date_add(last_day(add_months(<date_column>, -1)),1)

Upvotes: 9

Related Questions