How to cast field as date_type using databricks SQL

Question

The following Databricks SQL code should produce the following date_type outputs for fields:

Action-1-Analysing Action-2-Trying-to-meet Action-3-Date-agreed Action-4-Post-meeting Action-5-Chopped

The type of date_type output that should appear in the above fields is;

2019-01-04 12:09:09.0000000

The code is:

CREATE OR REPLACE VIEW curorigination.opportunitiespresentation

AS

WITH numbering AS (
    SELECT CompanyOwner, CurrentOpportunityStatus, LastDateStatusChanged, OpportunityName,
           rank() OVER (PARTITION BY CompanyOwner, CurrentOpportunityStatus ORDER BY  LastDateStatusChanged DESC) AS rank,
           row_number() OVER (PARTITION BY CompanyOwner, CurrentOpportunityStatus ORDER BY  LastDateStatusChanged DESC) AS row_number
    FROM   enrorigination.opportunities_hv
), Uni AS (
   SELECT CompanyOwner, CurrentOpportunityStatus, OpportunityName, 1 AS Sort, LastDateStatusChanged
   FROM  numbering
   WHERE  row_number = 1
   UNION ALL
   SELECT CompanyOwner, CurrentOpportunityStatus, OpportunityName, 2 AS Sort, LastDateStatusChanged
   FROM  numbering
   WHERE  rank = 1
)
SELECT CASE Sort WHEN 1 THEN CompanyOwner WHEN 2 THEN OpportunityName END AS `SumofLastDateStatusChanged`,
       CASE CurrentOpportunityStatus WHEN 'Action - 1. Analysing'      THEN LastDateStatusChanged END AS `Action-1-Analysing`,
       CASE CurrentOpportunityStatus WHEN 'Action - 2. Trying to meet' THEN LastDateStatusChanged END AS `Action-2-Trying-to-meet`,
       CASE CurrentOpportunityStatus WHEN 'Action - 3. Date agreed'    THEN LastDateStatusChanged END AS `Action-3-Date-agreed`,    
       CASE CurrentOpportunityStatus WHEN 'Action - 4. Post meeting'   THEN LastDateStatusChanged END AS `Action-4-Post-meeting`,  
       CASE CurrentOpportunityStatus WHEN 'Action - 5. Chopped'        THEN LastDateStatusChanged END AS `Action-5-Chopped`            
FROM   Uni       
ORDER BY CompanyOwner, CurrentOpportunityStatus, Sort, OpportunityName, LastDateStatusChanged

However, the I'm getting NULL outputs for the fields.

I believe this is because of the need to cast the field as a date_type, but I'm struggling on how to achieve this.

My attempt is as follows:

CREATE OR REPLACE VIEW curorigination.testdate

AS

WITH numbering AS (
    SELECT CompanyOwner, CurrentOpportunityStatus, LastDateStatusChanged, OpportunityName,
           rank() OVER (PARTITION BY CompanyOwner, CurrentOpportunityStatus ORDER BY  LastDateStatusChanged DESC) AS rank,
           row_number() OVER (PARTITION BY CompanyOwner, CurrentOpportunityStatus ORDER BY  LastDateStatusChanged DESC) AS row_number
    FROM   enrorigination.opportunities_hv
), Uni AS (
   SELECT CompanyOwner, CurrentOpportunityStatus, OpportunityName, 1 AS Sort, LastDateStatusChanged
   FROM  numbering
   WHERE  row_number = 1
   UNION ALL
   SELECT CompanyOwner, CurrentOpportunityStatus, OpportunityName, 2 AS Sort, LastDateStatusChanged
   FROM  numbering
   WHERE  rank = 1
)
SELECT CASE Sort WHEN 1 THEN CompanyOwner WHEN 2 THEN OpportunityName END AS `SumofLastDateStatusChanged`,
       CASE CurrentOpportunityStatus WHEN 'Action - 1. Analysing'      THEN LastDateStatusChanged CAST(`Action - 1. Analysing` AS DATE) END AS `Action-1-Analysing`,
       CASE CurrentOpportunityStatus WHEN 'Action - 2. Trying to meet' THEN LastDateStatusChanged CAST(`Action - 2. Trying to meet` AS DATE) END AS `Action-2-Trying-to-meet`,
       CASE CurrentOpportunityStatus WHEN 'Action - 3. Date agreed'    THEN LastDateStatusChanged CAST(`Action - 3. Date agreed` AS DATE) END AS `Action-3-Date-agreed`,    
       CASE CurrentOpportunityStatus WHEN 'Action - 4. Post meeting'   THEN LastDateStatusChanged CAST(`Action - 4. Post meeting` AS DATE) END AS `Action-4-Post-meeting`,  
       CASE CurrentOpportunityStatus WHEN 'Action - 5. Chopped'        THEN LastDateStatusChanged CAST(`Action - 5. Chopped` AS DATE) END AS `Action-5-Chopped`            
FROM   Uni       
ORDER BY CompanyOwner, CurrentOpportunityStatus, Sort, OpportunityName, LastDateStatusChanged

However, I'm getting the following error.

SELECT CASE Sort WHEN 1 THEN CompanyOwner WHEN 2 THEN OpportunityName END AS `SumofLastDateStatusChanged`,
       CASE CurrentOpportunityStatus WHEN 'Action - 1. Analysing'      THEN LastDateStatusChanged CAST(`Action - 1. Analysing` AS DATE) END AS `Action-1-Analysing`,
--------------------------------------------------------------------------------------------------^^^

I think the solution is simply placing the CAST function in the right place to make this work.