0

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            +
A Saraf
  • 315
  • 4
  • 20

1 Answers1

1

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;
nobody
  • 10,892
  • 8
  • 45
  • 63
  • This query is returning NULL values, is it because data_as_of_daily_date column is of String type in Staging layer. – A Saraf Jan 11 '19 at 17:07
  • @ASaraf DATE_SUB( string date, int days ) – nobody Jan 11 '19 at 17:09
  • Below is my query to test this and It is returning NULL as output select DATE_SUB(DATA_AS_OF_DAILY_DATE,DAY(DATA_AS_OF_DAILY_DATE) - 1), DATA_AS_OF_DAILY_DATE from table_name WHERE DATA_AS_OF_DAILY_DATE = '20181217'; – A Saraf Jan 11 '19 at 17:17
  • What does select DATA_AS_OF_DAILY_DATE from table_name WHERE DATA_AS_OF_DAILY_DATE = '20181217' get you? – nobody Jan 11 '19 at 18:27
  • select DATE_SUB(DATA_AS_OF_DAILY_DATE,DAY(DATA_AS_OF_DAILY_DATE) - 1) as firstday, DATA_AS_OF_DAILY_DATE from table_name WHERE DATA_AS_OF_DAILY_DATE = '20181217'; 1 NULL 20181217 2 NULL 20181217 3 NULL 20181217 – A Saraf Jan 11 '19 at 18:33