CODEWITHSUNDEEP
sqlsql-servert-sql
user1342164
user1342164

Reputation: 1454

SQL between 2 dates not returning correct results

I am using the query below and I would like to get the rows where "[Last Update Date]" is in either 2015 or 2016.

SELECT [NPI]
      ,[Entity Type Code]
      ,[Replacement NPI]
      ,[Employer Identification Number (EIN)]
      ,[Provider Organization Name (Legal Business Name)]
      ,[Provider Last Name (Legal Name)]
      ,[Provider First Name]
      ,[Provider Middle Name]
      ,[Provider Name Prefix Text]
      ,[Provider Name Suffix Text]
      ,[Provider Credential Text]
      ,[Provider Enumeration Date]
      ,[Last Update Date]
      ,[Healthcare Provider Taxonomy Code_1]

  FROM [Database].[dbo].[NPIInfo]
  WHERE[Healthcare Provider Taxonomy Code_1] in ('122300000X', '1223G0001X','1223P0221X','1223P0700X')
  AND ([Last Update Date] BETWEEN '2015/01/01' AND '2016/12/31' )
  order by [Last Update Date]

I don't receive any results when I run the code above. But if I run it with this "AND" statement I get results:

AND [Last Update Date] like '%2015%' or [Last Update Date] like '%2016%'

This result gives me 500,000 + rows.

If I run it this way per year I get way less results:

AND [Last Update Date] like '%2015%'

Any idea what I am doing wrong here? The last 2 And statements are giving me different results TIA

Upvotes: 1

Views: 2390

Answers (2)

Rominus
Rominus

Reputation: 1221

EDIT: Apparently I'm wrong here. Leaving the answer for reference as to what is not the problem. However, the CAST code might work anyway.

First, you can't use BETWEEN with text data, you have to do it with a datetime or numeric format. If [Last update Date] is text you'll need to convert it to datetime before you can do anything with it. It would be best to change your actual data's format, but if you can't for some reason this may work:

AND (CAST([Last Update Date] as datetime) 
BETWEEN CAST('2015/01/01' as datetime) AND CAST('2016/12/31' as datetime))

Regarding your like '%2015%' statements, the wildcard condition you're using is specifically for text data; that's why it works at all. The different row amounts are because the first like is looking for both 2015 and 2016, while the second one is looking for only 2015.

Upvotes: 2

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

TRY 

 WHERE CONVERT(DATETIME, [Last Update Date] , 103) BETWEEN '2015/01/01' AND '2016/12/31'

Check this page for the different formats

http://www.sql-server-helper.com/sql-server-2008/sql-server-2008-date-format.aspx

Upvotes: 3

Related Questions