0

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

enter image description here

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): enter image description here

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?

3

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;

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.