Clever Geek Handbook

Sql two monday night between

I have a problem with sql query. Now I have GETDATE () For example today is Wednesday, I need to have a date between Mondays. and GETDATE () will be on those two dates

Example today is Thursday 05/18/2017 I want a date between 05/15/2017 and 05/22/2017.

I haven't found any solution. How can I write it in the where clause in the query.

SELECT * FROM MATCHES
WHERE ...

Thank you in advance

+3


source to share


3 answers


To get the first Monday: SELECT DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 0)

and the second one: SELECT DATEADD(ww, DATEDIFF(ww,0,GETDATE()) + 1, 0)

http://joelabrahamsson.com/getting-the-first-day-in-a-week-with-t-sql/



also

Get first day of week in SQL Server

+1


source


here is the solution first create the calendar table then



declare @startDate datetime = dateadd(week, datediff(week, 0, getdate()), 0);
declare @endDate datetime = DATEADD(DAYS,7,@startDate)

SELECT  Date
FROM    dbo.Calendar
WHERE   Date >= @startDate 
AND     Date < @endDate ;
+1


source


Create calendar table if it doesn't exist

IF EXISTS (SELECT * FROM information_schema.tables WHERE Table_Name = 
'Calendar' AND Table_Type = 'BASE TABLE')
BEGIN
   DROP TABLE [Calendar]
END

CREATE TABLE [Calendar]
(
   [CalendarDate] DATETIME
)

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = GETDATE()
SET @EndDate = DATEADD(d, 365, @StartDate)

WHILE @StartDate <= @EndDate
BEGIN
      INSERT INTO [Calendar]
      (
            CalendarDate
      )
      SELECT
          @StartDate

         SET @StartDate = DATEADD(dd, 1, @StartDate)
END

Then use the below query to get the output

declare @start datetime = dateadd(week, datediff(week, 0, getdate()), 0);
declare @end datetime = DATEADD(DAY,8,@start)

SELECT [CalendarDate]
FROM Calendar
WHERE CalendarDate BETWEEN @start AND @end
0


source






More articles:


All Articles