1

For given any date in between in month, I need to get month_end_date and month_start_date in hive SQL

Month_start_date: is working fine as shown below img..

date_format(date_sub(DATA_DATE, (int(SUBSTR(expl.Time_Dim, 2,length(expl.Time_Dim))) - 1) * 7 ) , 'yyyy-MM-01') AS month_start_date

month_end_date: is giving sequence of numbers inbetween 32 and 9 for given code..

date_format(date_sub(DATA_DATE, (int(SUBSTR(expl.Time_Dim, 2,length(expl.Time_Dim))) - 1)),'yyyy-MM-DD') AS month_end_date,

enter image description here

luigigi
  • 4,146
  • 1
  • 13
  • 30
Anvesh
  • 97
  • 2
  • 11
  • `last_day(string date)`. First day can be calculated easier too - `date_format(current_date,'yyyy-MM-01')`. – Ben Watson Oct 26 '21 at 11:16
  • Hi Ben refeed to that hive link only. From that reference i formed month_start_date. But with Month_end_date i am facing problem. Need to take date values from ----`(expl.Time_Dim, 2,length(expl.Time_Dim))`----- to get month_end_date – Anvesh Oct 26 '21 at 11:55
  • 1
    Please can you update the question to show what `expl.Time_Dim` is? Also if you can calculate `month_start_date` correctly then you can just run `last_day(month_start_date)`. – Ben Watson Oct 26 '21 at 12:03
  • 1
    yeah last_day(month_start_date) worked restructured the whole query for this ... Thanks Ben :) – Anvesh Oct 26 '21 at 13:23

0 Answers0