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!
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.