Based on day fetch all dates - sql

Question

I have start date, end date and name of days. How can fetch all dates between those two dates of that specific days in sql?

example data:

start_date:4/11/2018
end_date: 5/11/2018
days: monday, thursday

expected output: all dates between start and end date which comes on monday and thursday and store them in table

updated my present code(not working)

; WITH CTE(dt)
AS
(
      SELECT @P_FROM_DATE
      UNION ALL
      SELECT DATEADD(dw, 1, dt) FROM CTE
      WHERE dt < @P_TO_DATE
)
INSERT INTO Table_name 
(
    ID
    ,DATE_TIME
    ,STATUS
    ,CREATED_DATE
    ,CREATED_BY
)
SELECT @P_ID
       ,(SELECT  dt  FROM CTE WHERE DATENAME(dw, dt) In ('tuesday','friday',null))
       ,'NOT SENT'
       ,CAST(GETDATE() AS DATE)
       ,@USER_ID

Which [DBMS](https://en.wikipedia.org/wiki/DBMS) are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... — , Apr 11 '18 at 11:13
Please **[edit]** your question and add some [sample data](http://plaintexttools.github.io/plain-text-table/) and the expected output based on that data. [Formatted text](http://stackoverflow.com/help/formatting) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). ([edit] your question - do **not** post code or additional information in comments) — , Apr 11 '18 at 11:13
Perhaps this is a good starting point: http://www.sqlservercentral.com/articles/calendar/145206/ — Thom A, Apr 11 '18 at 11:22

PSK · Answer 1 · 2018-04-11T11:57:24.323

3

Another approach for generating dates between ranges can be like following query. This will be faster compared to CTE or WHILE loop.

DECLARE @StartDate DATETIME = '2018-04-11'
DECLARE @EndDate DATETIME = '2018-05-15'

SELECT @StartDate + RN AS DATE FROM
(   
    SELECT (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))-1 RN 
    FROM   master..[spt_values] T1
) T 
WHERE RN <= DATEDIFF(DAY,@StartDate,@EndDate)
AND DATENAME(dw,@StartDate + RN) IN('Monday','Thursday')

Note:

If the row count present in master..[spt_values] is not sufficient for the provided range, you can make a cross join with the same to get a bigger range like following.

SELECT (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))-1 RN 
    FROM   master..[spt_values] T1
    CROSS JOIN master..[spt_values] T2

By this you will be able to generate date between a range with gap of 6436369 days.

edited Apr 11 '18 at 11:57

answered Apr 11 '18 at 11:37

PSK

17,547
5
32
43

A couple of notes on this: 1. [spt_values is undocumented](https://stackoverflow.com/a/4275271/3094533). 2. you can get rid of the outermost query and do it's where clause on the middle query. 3. Other then that, it's quite close to what I was about to post, so +1. – Zohar Peled Apr 11 '18 at 11:44
@ZoharPeled, thanks for the inputs, you are correct. I have removed the outer select. – PSK Apr 11 '18 at 11:47

score 2 · Answer 2 · answered Apr 11 '18 at 11:27

2

You can use a recursive common table expression (CTE) to generate a list of days. With datepart(dw, ...) you can filter for specific days of the week.

An example that creates a list of Mondays and Thursdays between March 1st and today:

create table ListOfDates (dt date);

with    cte as
        (
        select  cast('2018-03-01' as date) as dt  -- First day of interval
        union all
        select  dateadd(day, 1, dt)
        from    cte
        where   dt < getdate()  -- Last day of interval
        )
insert  into ListOfDates
        (dt)
select  dt
from    cte
where   datepart(dw, dt) in (2, 5)  -- 2=Monday and 5=Thursday
option  (maxrecursion 0)

See it working at SQL Fiddle.

answered Apr 11 '18 at 11:27

Andomar

232,371
49
380
404

`Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.` this is the error i am getting. – prasanna Apr 11 '18 at 12:33
Not sure how to explain that, the query above works on SQL Fiddle! – Andomar Apr 11 '18 at 13:56
`create table ListOfDates (dt date, name varchar(20)); with cte as ( select cast('2018-03-01' as date) as dt union all select dateadd(day, 1, dt) from cte where dt < getdate() ) insert into ListOfDates (dt, name) select (select dt from cte where datepart(dw, dt) in (2, 5) ),-- Monday and Thursday 'hello' ` try the same in fiddle – prasanna Apr 11 '18 at 14:07
The `select` part of a query cannot produce multiple rows. That has to be done in the `from` part. See here for a way to insert a name as well as a date: http://sqlfiddle.com/#!18/66678/1/0 – Andomar Apr 11 '18 at 18:13

score 1 · Answer 3 · answered Apr 11 '18 at 11:26

This will work for you:

DECLARE @table TABLE(
ID INT IDENTITY(1,1),
Date DATETIME,
Day VARCHAR(50)
)
DECLARE @Days TABLE(
ID INT IDENTITY(1,1),
Day VARCHAR(50)
)

INSERT INTO @Days VALUES ('Monday')
INSERT INTO @Days VALUES ('Thursday')




DECLARE @StartDate DATETIME='2018-01-01';
DECLARE @EndDate DATETIME=GETDATE();


DECLARE @Day VARCHAR(50)='Friday';

DECLARE @TempDate DATETIME=@StartDate;

WHILE CAST(@TempDate AS DATE)<=CAST(@EndDate AS DATE)
BEGIN

    IF EXISTS (SELECT 1 FROM @Days WHERE DAY IN (DATENAME(dw,@TempDate))) 
    BEGIN
        INSERT INTO @table
        VALUES  ( 
                  @TempDate, -- Date - datetime
                  DATENAME(dw,@TempDate)  -- Day - varchar(50)
                  )
    END

    SET @TempDate=DATEADD(DAY,1,@TempDate)
END



SELECT * FROM @table

score 1 · Answer 4 · answered Apr 11 '18 at 11:27

1

INSERT INTO TargetTab(dateCOL)    
SELECT dateCOL
    FROM tab
    WHERE dateCOL >= startdate AND dateCOL <= enddate 
    AND (DATENAME(dw,dateCOL) ='Thursday' OR DATENAME(dw,dateCOL) = 'Monday')

Try this query to get your result.

answered Apr 11 '18 at 11:27

Akshey Bhat

8,227
1
20
20

i did not understand what is `dateCol` – prasanna Apr 11 '18 at 13:08
Replace it with the name of your table's date column – Akshey Bhat Apr 11 '18 at 13:09

score 1 · Answer 5 · answered Apr 11 '18 at 11:34

Use a recursive CTE to generate your dates, then filter by week day.

SET DATEFIRST 1 -- 1: Monday, 7 Sunday

DECLARE @StartDate DATE = '2018-04-11'
DECLARE @EndDate DATE = '2018-05-15'

DECLARE @WeekDays TABLE (WeekDayNumber INT)

INSERT INTO @WeekDays (
    WeekDayNumber)
VALUES
    (1), -- Monday
    (4) -- Thursday

;WITH GeneratingDates AS
(
    SELECT
        GeneratedDate = @StartDate,
        WeekDay = DATEPART(WEEKDAY, @StartDate)

    UNION ALL

    SELECT
        GeneratedDate = DATEADD(DAY, 1, G.GeneratedDate),
        WeekDay = DATEPART(WEEKDAY, DATEADD(DAY, 1, G.GeneratedDate))
    FROM
        GeneratingDates AS G -- Notice that we are referencing a CTE that we are also declaring
    WHERE
        G.GeneratedDate < @EndDate
)
SELECT
    G.GeneratedDate
FROM
    GeneratingDates AS G
    INNER JOIN @WeekDays AS W ON G.WeekDay = W.WeekDayNumber
OPTION
    (MAXRECURSION 30000)

Michał Turczyn · Accepted Answer · 2018-04-11T15:28:33.913

Try this:

declare @start date = '04-11-2018'
declare @end date = '05-11-2018'
declare @P_ID int = 1
declare @USER_ID int = 11
;with cte as(
    select @start [date]
    union all 
    select dateadd(DAY, 1, [date]) from cte
    where [date] < @end
)
--if MY_TABLE doesn't exist
select @P_ID,
       [date],
       'NOT SENT',
       cast(getdate() as date),
       @USER_ID
into MY_TABLE
from cte
--here you can specify days: 1 - Sunday, 2 - Monday, etc.
where DATEPART(dw,[date]) in (2, 5)
option (maxrecursion 0)

--if MY_TABLE does exist
--insert into MY_TABLE
--select @P_ID,
--       [date],
--     'NOT SENT',
--     cast(getdate() as date),
--     @USER_ID
--from cte
--where DATEPART(dw,[date]) in (2, 5)
--option (maxrecursion 0)

@prasanna Please, try updated query. Also, you could delete commments, as they're irrelevant to discussion (I deleted mine). — Michał Turczyn, Apr 11 '18 at 14:51

Based on day fetch all dates - sql

6 Answers6