Hannah McDade
Hannah McDade

Reputation: 87

SQL View DateTime

I am trying to create a view from a table and append a new column to it which will give the user the date that it was recently updated... here is the create script that I am using and I am getting an error surrounding the syntax with 'datetime' - can anyone tell me what I'm doing wrong?

Create View [View].[WashingtonProspectiveBuyer] AS SELECT 
    [FirstName] AS FirstName, 
    [LastName] AS LastName, 
    CONCAT([AddressLine1], [AddressLine2], [City]) AS [Address], 
    [PostalCode] AS PostalCode,
    [Phone] AS Phone,
    [EmailAddress] AS EmailAddress,
    NULL As DateUpdated datetime NOT NULL DEFAULT GETDATE()   
FROM dbo.ProspectiveBuyer  WHERE [StateProvinceCode] = 'WA'

Upvotes: 1

Views: 481

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270663

You are confusing create table and create view. Perhaps you intend:

Create View [View].[WashingtonProspectiveBuyer] AS
    SELECT [FirstName] AS FirstName, 
           [LastName] AS LastName, 
           CONCAT([AddressLine1], [AddressLine2], [City]) AS [Address], 
           [PostalCode] AS PostalCode,
           [Phone] AS Phone,
           [EmailAddress] AS EmailAddress,
           COALESCE(DateUpdated, GETDATE()  ) as  DateUpdated
    FROM dbo.ProspectiveBuyer 
    WHERE [StateProvinceCode] = 'WA';

DEFAULT syntax is how you declare columns. It is used in CREATE TABLE statement, not in SELECT queries.

Upvotes: 2

Related Questions