In Databricks SQL, how can I convert a date in string format "2021Q2" to a timestamp with the last day of that quarter?

CodePudding user response：

select 
    to_timestamp(
       last_day(
          to_date(
            (left('2021Q4',4)||'-'||int(right('2021Q4',1)*3))||'-'||'1'))) 
from 
   my_table

CodePudding user response：

Simple way :

select to_timestamp(last_day(concat('2021','-',0,4*3,'-01'))) as last_date_queter

Logic :

• calculate the last month of quarter . by using multiple with 3. example 4th quarter's last month calculated 12 (4*3)
• concat (year,'-',-01) so that we can get the first day of respective month 2021-12-01
• last_day we can use last date of the given date month.
• finally , we can convert the date into timestamp to_timestamp