Search code examples
sqldatevertica

Remove char and convert Date format in Vertica / SQL

Remove the "w/o" and convert the date format into "MM/DD/YYYY" (Ex: 10/26/2020) in Sql/Vertica

Date
26-Oct-2020 w/o
02-Nov-2020 w/o
21-Jan-2021 w/o
Solution

  • Remove ' w/o' using REPLACE(), then convert the string to a date using TO_DATE(), and convert the date back to a string using TO_CHAR().

    WITH
-- your input ...
indata(dt) AS (
          SELECT '26-Oct-2020 w/o'
UNION ALL SELECT '02-Nov-2020 w/o'
UNION ALL SELECT '21-Jan-2021 w/o'
)
-- end of input - real query starts here
SELECT
  TO_CHAR(TO_DATE(REPLACE(dt,' w/o',''),'dd-Mon-yyyy'),'mm/dd/yyyy') AS refmt_date
FROM indata;
-- out  refmt_date 
-- out ------------
-- out  10/26/2020
-- out  11/02/2020
-- out  01/21/2021
-- out (3 rows)
-- out 
-- out Time: First fetch (3 rows): 15.929 ms. All rows formatted: 16.984 ms