CODEWITHSUNDEEP
hadoophivehiveqlimpala
A Saraf
A Saraf

Reputation: 275

Impala - First date of Month from string date

I have column data_as_of_daily_date (Data type String) in my source table (staging) and I need to find out First date of Month based on source table column in Impala and load it into target table having column FIRST_DAY_OF_MONTH (String type).

Source table:

+--------------------+
data_as_of_daily_date
+--------------------+
+20181217            +
+20181115            +

I am running below query:

SELECT 
      DATE_ADD(FROM_UNIXTIME(UNIX_TIMESTAMP(DATA_AS_OF_DAILY_DATE, 'yyyyMMdd')), 1 - DAY(FROM_UNIXTIME(UNIX_TIMESTAMP(DATA_AS_OF_DAILY_DATE, 'yyyyMMdd')))) AS FIRST_DAY_OF_MONTH
FROM TABLE;

Output:

+--------------------+
AS FIRST_DAY_OF_MONTH
+--------------------+
+2018-12-01 00:00:00 +
+2018-11-01 00:00:00 +

Expected Output:

+--------------------+
AS FIRST_DAY_OF_MONTH
+--------------------+
+20181201            +
+20181101            +

Upvotes: 0

Views: 4382

Answers (1)

nobody
nobody

Reputation: 11090

No need to use from_unixtime,unix_timestamp unless you are changing the format.

select DATE_SUB(data_as_of_daily_date,DAY(data_as_of_daily_date) - 1) 
from table;

Upvotes: 1

Related Questions