CODEWITHSUNDEEP
sqlsql-serverazurecalculated-columns
DarthCSharper
DarthCSharper

Reputation: 133

MS SQL Server - Adding computed column with subquery

I am trying to add computed column with subquery which will transform Unix timestamp to regular DateTime format, using this query, but there is error. Using Azure SQL Server. 

ALTER TABLE dbo.FP_Contacts_CRM 
    ADD DWH_propertieslastmodifieddatevalue 
        AS (SELECT DATEADD(S, CONVERT(INT, LEFT(propertieslastmodifieddatevalue, 10)), '1970-01-01')
            FROM dbo.FP_Contacts_CRM)

I get this error:

Msg 1046, Level 15, State 1, Line 12
Subqueries are not allowed in this context. Only scalar expressions are allowed

Upvotes: 2

Views: 585

Answers (2)

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30663

As the error says, subqueries are not allowed for the computed column declaration.

But for the original question, you can use use scalar functions like below.

DATEADD(S, CONVERT(int,LEFT(<yourdtcolumn>, 10)), '1970-01-01')

Upvotes: 2

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8043

You can't use subqueries for Calculated Columns. Instead, just give the formula only.

alter table dbo.FP_Contacts_CRM 
add DWH_propertieslastmodifieddatevalue as 
    (
        DATEADD(S, 
                    CONVERT(int,LEFT(propertieslastmodifieddatevalue, 10))
                    , '1970-01-01')
    )

Upvotes: 3

Related Questions