datetime column returning a zero when queried from a view

Question

when I run the following query it returns me a zero, although the column is a datetime type.

    SELECT IVSubmitted 
FROM vw_queue_ready_for_agencyReview 
WHERE agencyid = 1399 
ORDER BY lastName ASC, firstName ASC

this is the code for the view:

IF OBJECT_ID('[dbo].[vw_queue_ready_for_agencyReview]') IS NOT NULL 
DROP  VIEW      [dbo].[vw_queue_ready_for_agencyReview] 
GO


CREATE VIEW [dbo].[vw_queue_ready_for_agencyReview]
AS
 SELECT t1.*, t2.activityDate as IVSubmitted 
 FROM vw_applicant_queue_output t1
 LEFT JOIN applicationActivity t2 on t1.applicationID = t2.applicationID
  AND t2.activityTypeID = 48
  AND t2.activityDate IN (SELECT min(activityDate)
        FROM applicationActivity
        WHERE applicationID = t2.applicationID
         AND activityTypeID = 48)         
 WHERE applicationReviewStatus = 3
   AND status = 'OLA'

the other view:

CREATE VIEW [dbo].[vw_applicant_queue_output]
AS
SELECT   a.firstName,
    a.lastName,
    a.birthDate,
    a.emailAddress,
    dbo.ftn_age_calculation(a.birthDate) AS Age,
    datediff( day, a.createDate, getdate() ) As DaysSinceRegistration,
    a.applicantID,
    ap.applicationID,
    ap.status,
    ad.availabilityEarliestDate,
    ad.availabilityLatestDate,
    ap.programID,
    uvs.lastLogin,
    uvs.lastAction,
    ct.firstName     AS InterviewerFirstName,
    ct.lastName      AS InterviewerLastName,
    asd.applicationFormStatus,
    asd.hostFamilyLetterStatus,
    asd.childcareExperienceStatus,
    asd.applicationReviewStatus,
    asd.uploadStatus,
    asd.interviewReportStatus,
    asd.referenceCheckStatus,
    ap.agencyID,
    ap.interviewerID,
    ap.sendBrochure,
    ap.brochureSent,
    ac.applicationForm,
    ac.childcareExperience,
    ac.hostFamilyLetter,
    ac.uploads,
    ac.interviewReport,
    ac.telephoneReferenceCheck,
    ac.agency_applicationForm,
    ac.agency_childcareExperience,
    ac.agency_hostFamilyLetter,
    ac.agency_uploads,
    ac.agency_interviewReport,
    ac.agency_telephoneReferenceCheck,
    ct.branchID,
    ct.isBranchManager,
    a.requiresCallback,
    a.calledBackDate,
    a.archive,
    a.leadType,
    a.grading,
    ag.countryCode   AS 'agencyCountryCode'
FROM   applicant    AS a 
 JOIN  application    AS ap ON ap.applicantID  = a.applicantID  AND ap.isActive  = 1
 JOIN  applicationData   AS ad ON ad.applicationID  = ap.applicationID 
 LEFT JOIN agency     AS ag ON ag.agencyID   = ap.agencyID
 LEFT JOIN applicationStatus  AS asd ON asd.applicationID = ap.applicationID
 LEFT JOIN contact     AS ct ON ct.contactID   = ap.interviewerID AND ct.interviewer = 1
 LEFT JOIN userVisitSummary  AS uvs ON uvs.uniqueUserID  = ap.applicationID AND uvs.recordType = 'APP'
 LEFT JOIN applicationChecklist AS ac ON ac.applicationID  = ap.applicationID
WHERE   ap.status   NOT IN ('ARC', 'REJ' ) 

the table dbo.applicationActivity, which contains the column in question activityDate:

CREATE TABLE [dbo].[applicationActivity] ( 
[applicationActivityID]  INT              IDENTITY(1,1)   NOT NULL,
[applicationID]          INT                              NOT NULL,
[activityTypeID]         INT                              NOT NULL,
[activityBy]             VARCHAR(100)                     NOT NULL,
[activityDate]           DATETIME                             NULL  CONSTRAINT [DF__applicati__activ__685E57C7] DEFAULT (getdate()),
[objectTypeID]           INT                                  NULL,
[objectType]             VARCHAR(10)                          NULL,
[sessionID]              VARCHAR(100)                         NULL,
CONSTRAINT   [PK__applicationActiv__676A338E]  PRIMARY KEY CLUSTERED    ([applicationActivityID] asc),
CONSTRAINT   [FK_applicationActivity_application]                                FOREIGN KEY ([applicationID]) REFERENCES [application]([applicationID]),
CONSTRAINT   [FK_applicationActivity_activityType]                               FOREIGN KEY ([activityTypeID]) REFERENCES [activityType]([activityTypeID]))

GO

CREATE NONCLUSTERED INDEX [idx_activityDate] 
   ON [dbo].[applicationActivity] ([activityDate] desc, [activityBy] asc)
   INCLUDE ([applicationID], [activityTypeID], [objectTypeID], [objectType])
CREATE NONCLUSTERED INDEX [idx_applicationid] 
   ON [dbo].[applicationActivity] ([applicationID] asc)
CREATE NONCLUSTERED INDEX [idx_objectTypeID] 
   ON [dbo].[applicationActivity] ([objectTypeID] asc, [objectType] asc)
   WHERE ([objectType] IS NOT NULL)

The query plan for the original query

BUT when I copy the code of the first view into the query and run it again:

SELECT IVSubmitted  FROM 
(

 SELECT t1.*, t2.activityDate as IVSubmitted 
 FROM vw_applicant_queue_output t1
 LEFT JOIN applicationActivity t2 on t1.applicationID = t2.applicationID
  AND t2.activityTypeID = 48
  AND t2.activityDate IN (SELECT min(activityDate)
        FROM applicationActivity
        WHERE applicationID = t2.applicationID
         AND activityTypeID = 48)         
 WHERE applicationReviewStatus = 3
   AND status = 'OLA'

) r
 WHERE agencyid = 1399 ORDER BY lastName ASC, firstName ASC

It then gives me the expected result (a datetime):

the query plan for this new query.

when running the following query:

   select @@version as [my sql server],
   d.compatibility_level
   from sys.databases d
   where d.database_id = db_id()

my sql server
Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Datacenter 6.3 (Build 9600: ) (Hypervisor)

compatibility_level
100

What is causing this behaviour and how can I return the datatime?

Answer 1

As Mark Sinkinson pointed out in his comment, the problem is well described here: Bad habits to kick : using SELECT * / omitting the column list

As the title suggests, your problem was in using * instead of column list in the view.

When I saw * in your code, my first thought was about this problem so I suggested you to use sp_refreshview.

Of course it's not a solution, it was just a small test to understand if the problem was exactly there. The real solution is to rewrite your view with the complete column list instead of *

Here is my small repro to show how it could happen:

create table dbo.t (id int, dt_col datetime, int_col int);
GO

create view dbo.vw_test as 
select *
from dbo.t;
GO

alter table dbo.t drop column dt_col;
go

alter table dbo.t add dt_col datetime;
go

insert dbo.t(id, dt_col, int_col)
values (1, '20180209', 0);

select dt_col
from dbo.vw_test;