-1

I have the below query

SELECT  b.CreateDate, *
FROM         ConsolidatedData_DealSheet AS a INNER JOIN
                      Deals AS b ON a.DealNo = b.DealNo

Result Set

2007-11-13 15:40:15.627 07110001    Atlantic
2007-11-13 16:27:13.783 07110002    Midwest
2007-11-13 17:18:47.783 07110003    Midwest
2007-11-16 14:06:27.917 07110004    Atlantic
2007-11-20 12:49:17.343 07110005    Midwest
2007-11-21 10:57:23.903 07110006    Midwest

If I change the query and add a date filter shown below

SELECT  b.CreateDate, *
FROM         ConsolidatedData_DealSheet AS a INNER JOIN
                      Deals AS b ON a.DealNo = b.DealNo
WHERE     
  b.CreateDate  BETWEEN  
  CONVERT(datetime, '11/16/2007') AND  DATEADD(day,1,CONVERT(datetime,  '11/21/2008'))

This is basically just joining ConsolidatedData_DealSheet and Deals and then filtering on the Deals.CreateDate but I don't get any result back.

If I just do

select * from Deals where Createdate between   CONVERT(datetime, '11/16/2007') AND  DATEADD(day,1,CONVERT(datetime,  '11/23/2008'))

I get all the data.

Can someone have a look and let me know what is wrong with my filter after the Inner join. I am not getting any data at all.

Script not getting any data with where clause

 SELECT  b.CreateDate, *
    FROM         ConsolidatedData_DealSheet AS a INNER JOIN
                          Deals AS b ON a.DealNo = b.DealNo
    WHERE     
      b.CreateDate  BETWEEN  
      CONVERT(datetime, '11/16/2007') AND  DATEADD(day,1,CONVERT(datetime,  '11/21/2008'))

Thanks

closed as unclear what you're asking by Max Vernon, MDCCL, Michael Green, Paul White Feb 20 at 2:47

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

3

I can't think of any other logical explanations:

  1. You are running the queries against different servers or databases.

  2. The datatype of the column is sql_variant. This has the result - for the comparison to be performed - that the lower precedence datatype (CONVERT(datetime, '11/16/2007') which is datetime) is converted to the highest precedence datatype (b.CreateDate which is sql_variant). The same happens for the 2nd comparison of the BETWEEN condition. As a result the comparisons are not done on datetimes but on the internal format that the values get when converted to sql_variant type.

    And that format does not off course preserve date order. It couldn't because it can store values of any type and can't be possibly preserving date and int and float and varchar and ... and datetime order at the same time.

Test in dbfiddle.uk

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