0

My problem is that EOMONTH doesn't seem to exist in Impala so I was hoping there is a substitute for EOMONTH. I just want to only return the values that correspond with end of month dates. Below is the query I tried and the last line is where I have the problem.

SELECT a.ticker,a.FieldName,a.DataSource,b.tickervalue, 
to_timestamp(CAST(tickervaluedate/1000 as bigint)) as tickervaluedate
FROM agronomycn.raw_crttickername AS a 
INNER JOIN agronomycn.raw_crttickervalue as b on a.TickerID = b.TickerID
WHERE a.ticker = 'C_FRONT2' AND a.fieldname = 'CLOSE' AND a.datasource = 'EXCH'
AND to_timestamp(CAST(b.tickervaluedate/1000 as BIGINT)) BETWEEN '2010-01- 01' AND '2019-01-01' 
AND to_timestamp(CAST(b.tickervaluedate/1000 as BIGINT)) = EOMONTH(to_timestamp(CAST(b.tickervaluedate/1000 as BIGINT)))
MLS
  • 108
  • 14

1 Answers1

2

This can be done with a combination of trunc,add_months and date_add.

select date_add(add_months(trunc(dtCol,'MM'),1),-1)

This assumes the string format of date is yyyy-MM-dd or yyyy-MM-dd HH:mm:ss.

Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
  • So when I change my last line to AND to_timestamp(CAST(b.tickervaluedate/1000 as BIGINT)) = date_add(add_months(trunc(to_timestamp(CAST(b.tickervaluedate/1000 as BIGINT)),'MM'),1),-1), I don't retrieve any values so I am assuming I am using this wrong – MLS Feb 20 '19 at 15:51
  • the format is timestamp but it's raw form is unixtime – MLS Feb 20 '19 at 15:55
  • ticker fieldname datasource tickervalue tickervaluedate ticker fieldname datasource tickervalue tickervaluedate 1 C_FRONT2 CLOSE EXCH 382.25 2018-11-12 06:00:00 2 C_FRONT2 CLOSE EXCH 377.75 2018-11-13 06:00:00 – MLS Feb 20 '19 at 16:00
  • just use `date_add(add_months(trunc(b.tickervaluedate,'MM'),1),-1)` to get the month end date. – Vamsi Prabhala Feb 20 '19 at 16:02
  • Yes that returns month end date but I want to only see values where it is a month end date for example values where date is 2018-12-31, 2018-11-30, etc. – MLS Feb 20 '19 at 16:03
  • compare it with the actual truncated part of date..`date_add(add_months(trunc(b.tickervaluedate,'MM'),1),-1) = to_date(b.tickervaluedate)` – Vamsi Prabhala Feb 20 '19 at 16:05
  • Perfect! Thank you so much. – MLS Feb 20 '19 at 16:09