I need your help with the following SQL query which worked fine during 2016 but not working now in 2017. So if you can take a look and let me know what I need to change to make it bring the 2017 results back I would appreciate it. If I comment out the part after [and] below it brings results but much more than I need
select LABEL AS 'Holiday', DATENAME(DW,HOLIDAYDATE) AS 'Day of the Week', CONVERT(VARCHAR(12), HOLIDAYDATE, 107) AS [Date] from HOLIDAYS where
year(HOLIDAYDATE) = YEAR(getdate()) and LOCATIONCODE = 'US'
Here's an abbreviated table below:
CREATE TABLE [dbo].[HOLIDAYS](
[COUNTRYCODE] varchar NULL,
[HOLIDAYDATE] [datetime] NULL,
[LOCATIONCODE] varchar NULL,
[UPDATEOPERATOR] varchar NULL,
[LABEL] varchar NULL,
It is probably because you don't have the holidays for 2017 inserted into your HOLIDAYS table.
SELECT * FROM dbo.HOLIDAYS WHERE HOLIDAYDATE < '20170101' -- gives you holidays in the table for 2016 and earlier
SELECT * FROM dbo.HOLIDAYS WHERE HOLIDAYDATE >= '20170101' -- gives you holidays in the table for 2017 and later.
I suspect the second query will return no rows at all. If that is the case, you need to insert the holidays for 2017 into that table.
Thank you James. Silly me, the table did not have entries for 2017 for the countries I was testing for.