0

I have a ‘Response_Date ‘ column that has VARCHAR datatype. This shows eastern time. The date is 3/31/2023 22:30. I am needing to change the format to date 3/31/2023 12:00:00 AM. When I use to_timestamp(Response_Date,’yyyy-MM-dd) as RES_DT, the date becomes 04/01/2023 12:00:00 AM. This is Impala environment. How do I get the March date instead of April?

When I use to_timestamp(Response_Date,’yyyy-MM-dd') as RES_DT, the date becomes 04/01/2023 12:00:00 AM. (I am expecting 03/31/2023 12:00:00 AM) I am also creating a new column 'Month_Of' off of the response date. I am expecting 03/01/2023 12:00:00 AM but it gives me 04/01/2023 12:00:00 AM Trunc(to_timestamp(Response_Date,’yyyy-MM-dd'), 'month') as Month_of

How do I get the March date instead of April?

user21529908
  • 1

1 Answers1

0

can you pls try

to_timestamp(split_part(Response_Date,' ',1),'MM/dd/yyyy') as Response_Date_truncated

i think to_timestamp is trying to convert to a timestamp considering the time. Remove the time part using split_part() and i think you should be good.

Koushik Roy
  • 6,868
  • 2
  • 12
  • 33