CODEWITHSUNDEEP
t-sqlsql-server-2005datetimecomparison
Albert Laure
Albert Laure

Reputation: 1722

Is this a Date Comparison bug or in one of my field?

Good Day Every One

as i create queries for my project i stumbled upon this bug

when i use this code

DECLARE @MONTH VARCHAR(10)
SET @MONTH = 'MAY'
DECLARE @YEAR VARCHAR(4)
SET @YEAR= '2012'

    SELECT COUNT (*) AS CYJEWELRY
    FROM Transactions.tbl_PawnItem PIT
    INNER JOIN Transactions.tbl_PawnMain PMN
    ON PIT.fld_PawnMainID= PMN.fld_PawnMainID
    INNER JOIN Transactions.tbl_PawnHisto PHI
    ON PHI.fld_PawnMainID = PMN.fld_PawnMainID
    WHERE PMN.fld_StatusID = 3 /* OR PMN.fld_StatusID = 4*/ 
    AND DATEADD(month,DATEDIFF(month,0,PHI.fld_LoanDate),0) =  DATEADD(month,DATEDIFF(month,0,DATEADD (MONTH,-4,@MONTH+@YEAR)),0)
    AND PMN.fld_StorageGroupID  >= 3 and PMN.fld_StorageGroupID <= 14

The results are CYJEWELRY = 23

When i use the status ID 4 like this

DECLARE @MONTH VARCHAR(10)
SET @MONTH = 'MAY'
DECLARE @YEAR VARCHAR(4)
SET @YEAR= '2012'

    SELECT COUNT (*) AS CYJEWELRY
    FROM Transactions.tbl_PawnItem PIT
    INNER JOIN Transactions.tbl_PawnMain PMN
    ON PIT.fld_PawnMainID= PMN.fld_PawnMainID
    INNER JOIN Transactions.tbl_PawnHisto PHI
    ON PHI.fld_PawnMainID = PMN.fld_PawnMainID
    WHERE /*PMN.fld_StatusID = 3  OR */ PMN.fld_StatusID = 4
    AND DATEADD(month,DATEDIFF(month,0,PHI.fld_LoanDate),0) =  DATEADD(month,DATEDIFF(month,0,DATEADD (MONTH,-4,@MONTH+@YEAR)),0)
    AND PMN.fld_StorageGroupID  >= 3 and PMN.fld_StorageGroupID <= 14

The results are CYJEWELRY = 34

The question is why does when i use both of them like this

DECLARE @MONTH VARCHAR(10)
SET @MONTH = 'MAY'
DECLARE @YEAR VARCHAR(4)
SET @YEAR= '2012'

    SELECT COUNT (*) AS CYJEWELRY
    FROM Transactions.tbl_PawnItem PIT
    INNER JOIN Transactions.tbl_PawnMain PMN
    ON PIT.fld_PawnMainID= PMN.fld_PawnMainID
    INNER JOIN Transactions.tbl_PawnHisto PHI
    ON PHI.fld_PawnMainID = PMN.fld_PawnMainID
    WHERE PMN.fld_StatusID = 3  OR  PMN.fld_StatusID = 4
    AND DATEADD(month,DATEDIFF(month,0,PHI.fld_LoanDate),0) =  DATEADD(month,DATEDIFF(month,0,DATEADD (MONTH,-4,@MONTH+@YEAR)),0)
    AND PMN.fld_StorageGroupID  >= 3 and PMN.fld_StorageGroupID <= 14

the results turn to be CYJEWELRY = 380?(i know this is wrong) instead of 57?

any tips? or suggestions? to improve my code and remove the bug?? that would be higly appreciated by me im new in t-sql please be gentle :) thank you so much :)

Upvotes: 0

Views: 34

Answers (1)

Adriaan Stander
Adriaan Stander

Reputation: 166396

Yuo need to add brackets around the OR

Something like

WHERE (PMN.fld_StatusID = 3  OR  PMN.fld_StatusID = 4)
AND DATEADD(month,DATEDIFF(month,0,PHI.fld_LoanDate),0) =  DATEADD(month,DATEDIFF(month,0,DATEADD (MONTH,-4,@MONTH+@YEAR)),0)
AND PMN.fld_StorageGroupID  >= 3 and PMN.fld_StorageGroupID <= 14

When you look at the execution precedance

Writing

WHEN a = b 
OR   c = d
AND  e = f

this is the same as saying

WHEN a = b 
OR   (c = d AND  e = f)

whereas you intentions where

WHEN (a = b OR c = d)
AND  e = f

Have a look at Logical Operator Precedence

When more than one logical operator is used in a statement, NOT is evaluated first, then AND, and finally OR. Arithmetic, and bitwise, operators are handled before logical operators.

Also look at the table in Operator Precedence (Transact-SQL)

Upvotes: 1

Related Questions