Post reply

CAST AS DATE Assistance

  • July 4, 2022 at 8:59 pm

    #4058610

    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

     

  • July 5, 2022 at 12:19 am

    #4058845

    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)

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

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