I am using the query below and I would like to get the rows where "[Last Update Date]" is in either 2015 or 2016.
,[Entity Type Code]
,[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]
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]
AND [Last Update Date] like '%2015%' or [Last Update Date] like '%2016%'
AND [Last Update Date] like '%2015%'
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))
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.