Search code examples
sqlhadoopclouderaimpala

Impala - First day of this month

I have variable name date in a timestamp format. I am trying to get the first day of the month. For example if the date is 2022-02-27 Then the output I need is 2022-02-01. Another example would be if the date is 2022-01-15 Then the output I need is 2022-01-01

I need the first day of that month from that date field. I am doing this in Impala, I tried couple of things, but it did not worked.

Thank you for your help!

Solution

  • you can try any one of below -

    select 
trunc(datetime_col,'MM') first_day,
to_timestamp(concat(from_timestamp(datetime_col,'yyyyMM'),'01'),'yyyyMMdd') first_day_2,
to_timestamp(concat( substr(string_col,1,8),'01'),'yyyy-MM-dd') first_day_2_str -- when your date time column is a string

    first method - truncating Month part from timestamp column to first day of month.

    second method - so, you are getting yyyyMM from your timestamp column, and then add 01 to that string to get first day string. Then you can convert it to timestamp.

    third method - similar to second method but substr is used to cut the month part.