CAST AS DATE Assistance

  • Can someone let me know why I'm not able to use CAST method to cast a field as date

     

    ;WITH numbering AS (
    SELECT MontaguOwner, CurrentOpportunityStatus, LastDateStatusChanged, OpportunityName,
    rank() OVER (PARTITION BY MontaguOwner, CurrentOpportunityStatus ORDER BY LastDateStatusChanged DESC) AS rank,
    row_number() OVER (PARTITION BY MontaguOwner, CurrentOpportunityStatus ORDER BY LastDateStatusChanged DESC) AS row_number
    FROM enrorigination.opportunities_hv
    ), Uni AS (
    SELECT MontaguOwner, CurrentOpportunityStatus, OpportunityName, 1 AS Sort, LastDateStatusChanged
    FROM numbering
    WHERE row_number = 1
    UNION ALL
    SELECT MontaguOwner, CurrentOpportunityStatus, OpportunityName, 2 AS Sort, LastDateStatusChanged
    FROM numbering
    WHERE rank = 1
    )
    SELECT CASE Sort WHEN 1 THEN MontaguOwner 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
    FROM Uni
    ORDER BY MontaguOwner, CurrentOpportunityStatus, Sort, OpportunityName, LastDateStatusChanged

    I'm sure it''s something very simple that I'm missing

     

  • CAST('Action - 1. Analysing' AS DATE) END

    'Action -1. Analysing' is a string that cannot be coerced to date. If that's supposed to be a column in your table, then wrap it in brackets

    CAST([Action - 1. Analysing] AS DATE)

  • This was removed by the editor as SPAM

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply