CODEWITHSUNDEEP
sqlsql-servert-sql
mHelpMe
mHelpMe

Reputation: 6668

Sql Server Date Query not working

I have a query, shown below. However when I run this query I get rows whose [date_effective] is prior to 14th Feb 2014. Why? I have dates of 1/1/1980 showing up.

  SELECT * 
    FROM D_CORPACTIONS_MSCI
   WHERE [date_effective] >= '14 February 2014' 
     AND [ca_status] = 'EXPECTED' 
      OR [ca_status] = 'CONFIRMED' 
      OR [ca_status] = 'UNDETERMINED'
ORDER BY [date_effective]

Upvotes: 0

Views: 140

Answers (3)

Ronnie Rahman
Ronnie Rahman

Reputation: 1

Patrick Hofman and Kashif - they have already answered your question.

You can also use UNION ALL

SELECT * 
    FROM D_CORPACTIONS_MSCI
    WHERE ([date_effective] >= '14 February 2014') 
    AND [ca_status] = 'EXPECTED' 
UNION ALL
    SELECT * 
    FROM D_CORPACTIONS_MSCI
    WHERE ([date_effective] >= '14 February 2014') 
    AND [ca_status] = 'CONFIRMED' 
UNION ALL
    SELECT * 
    FROM D_CORPACTIONS_MSCI
    WHERE ([date_effective] >= '14 February 2014') 
    AND [ca_status] = 'UNDETERMINED')
ORDER BY [date_effective]

The UNION ALL performs better than OR.

Comparing – “OR” vs. “UNION ALL” My understanding as per below:

  1. The “OR” version query has two operators (steam aggregation and merge join) whereas “UNION ALL” version has concatenation operator only.
  2. Cardinality estimate is inaccurate for “OR” version query.
  3. The sub-tree cost for “UNION ALL” query is way less than the “OR” version.

Upvotes: 0

Patrick Hofman
Patrick Hofman

Reputation: 156928

Your and and or are conflicting each other.

Therefore the matched rows aren't correct.

Try to encapsulate the or statements or, it this case, use an in:

select *
from   d_corpactions_msci
where  date_effective >= '14 february 2014'
and    ca_status in ('EXPECTED', 'CONFIRMED', 'UNDETERMINED')
order
by     date_effective

Also, don't rely on the date format on client or server by converting the date:

where  date_effective >= convert(datetime, '20140214', 112)

Upvotes: 2

Kashif
Kashif

Reputation: 14430

SELECT * 
FROM   d_corpactions_msci 
WHERE  ( date_effective >= '14 February 2014' ) 
       AND ( ca_status = 'EXPECTED' 
              OR ca_status = 'CONFIRMED' 
              OR ca_status = 'UNDETERMINED' ) 
ORDER  BY date_effective

Upvotes: 2

Related Questions