SQL Server Data Types Reference - ConnectionStrings.com

SQL Server Data Types Reference - ConnectionStrings.com

Smalldatetime type issue

Recently, I’ve encountered an interesting situation.

createDateTime is Smalldatetime type

If using parameters , @datefrom and @end where they are DATETIME type, query is not running at all

After investigation, it appears that execution plan shows differently for query when using hardingcode and parameters.

In order to solve this issue, we have to use Query hint

 

When I cast DATETIME to SMALLDATETIME , it’s running …

select

COUNT(uf.uploadFileID),

--COUNT(ub.uploadBatchID),

ub.appContextID,

CONVERT(DATE,uf.createDateTime)

from dbo.UploadFile uf (nolock) JOIN dbo.UploadBatch ub (nolock)

on uf.uploadBatchID= ub.uploadBatchID

where uf.createDateTime >= @datefrom   ---->>>> small datetime type

and uf.createDateTime < @end

and uf.uploadFileStatusID = 0 -- success

and ub.uploadBatchStatusID = 0 -- success

and ub.appContextID in

(

2542704 --, -- previous android appcontextID notes like '%Partners 1993%'

--2167274 -- current android appContextID ac.notes like '%android%'

)

GROUP BY

ub.appContextID,

CONVERT(DATE,uf.createDateTime)