1

I have been wondering is there any operator/function in sql server to tell about weekday between given day.

Example :11-19-2016 to 11-29-2016 I want check if there is tuesday between that day?

davin kurniadi
  • 19
  • 4
  • 1
    [This question](http://stackoverflow.com/questions/18920393/sql-server-get-next-relative-day-of-week-next-monday-tuesday-wed) looks at finding the next `x` day after a given date. So, you could use that with your start date, and then compare whether the date you've produced is less than your end date. – Damien_The_Unbeliever Nov 29 '16 at 08:00
  • 1
    (And that is, of course, ignoring the obvious first check you can make - if there's more than 7 days between start and end date, as per your example, then of course the answer is yes) – Damien_The_Unbeliever Nov 29 '16 at 08:02
  • use DatePart :https://msdn.microsoft.com/en-us/library/ms174420.aspx – TheGameiswar Nov 29 '16 at 08:03

2 Answers2

1

Try This:

declare @start varchar(100)='11-19-2016'
declare @end varchar(100)='11-29-2016'

;with dateRange as
(
  select date = dateadd(dd, 1, @start)
  where dateadd(dd, 1, @start) < @end
  union all
  select dateadd(dd, 1, date)
  from dateRange
  where dateadd(dd, 1, date) < @end
)
select  date,DATENAME(dw,CAST(DATEPART(m, GETDATE()) AS VARCHAR) 
  + '/'+ CAST(DATEPART(d, date) AS VARCHAR) 
  + '/'+ CAST(DATEPART(yy, getdate()) AS VARCHAR)) as 'Day'
from dateRange where (DATENAME(dw,CAST(DATEPART(m, GETDATE()) AS VARCHAR) 
  + '/'+ CAST(DATEPART(d, date) AS VARCHAR) 
  + '/'+ CAST(DATEPART(yy, getdate()) AS VARCHAR)))='Tuesday'
Ranjana Ghimire
  • 1,785
  • 1
  • 12
  • 20
1

The query below uses a recursive query to unfold the date range.

Then uses DATEPART to select only the Tuesdays from it.
But one could also use DATENAME instead. 

declare @StartDate DATE = '2016-11-19';
declare @EndDate DATE = '2016-11-29';

--SET DATEFIRST 7;

-- The dw for tuesday is 3 when @@datefirst = 7 (default setting)
-- Since it depends on a usersetting, lets calculate it anyway.
declare @TuesdayWeekday INT = (7-@@datefirst + 2)%7+1;

;with DATES as
(
  select @startdate as [Date]
  union all
  select dateadd(day, 1, [Date]) from DATES
  where [Date] < @enddate
)
select [Date], datepart(dw,[Date]) as day_of_week, datename(dw,[Date]) as day_of_weekname
from DATES
where datepart(dw,[Date]) = @TuesdayWeekday;
LukStorms
  • 28,916
  • 5
  • 31
  • 45
  • 2
    "Since DATEPART starts the week at Sunday" - maybe it does - on *your* machine. But it's by no means universally true. See [DATEFIRST](https://msdn.microsoft.com/en-GB/library/ms181598.aspx). But I'd usually advise not messing with the datefirst setting - instead just pick an arbitrary known date that has the property you want and use that instead of a constant - e.g. `where datepart(dw,[Date]) = datepart(dw,'20150714'); --Tuesday` – Damien_The_Unbeliever Nov 29 '16 at 08:55