Asistant with simple query

1

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,

2

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.

1 Like
3

Thank you James. Silly me, the table did not have entries for 2017 for the countries I was testing for.