I would like to ask you for a help with the query. I´m trying to get list of random dates from 7.2.2016 - 14.2.2016, but I would like to exclude from it 11. and 12.2.2016, which will be weekends.
This is, what I have:
SELECT DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 8), '2016-02-07')
I´m using SQL server 2016.
and the dates should be allocated randomly to other columns:
SMS_send_day should be those dates, excluding 11.2. and 12.2.
Thank you for your advices!
Not sure if this helps, but this gets you a single date excluding the two you asked for. I assume you meant 2017, because those days weren't weekends in 2016.
declare @date date
while 1=1
begin
select @date = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 8), '2017-02-07')
if @date not in ('2/11/2017', '2/12/2017')
break
end
select @date
Edit Saw the comment...If a list is needed you can try something like this: t-sql get all dates between 2 dates and then just use a where clause to filter out the weekend dates.
If you get yourself a calendar table and you have a very simple query on your hands `
SELECT TOP 1 Date
FROM Calendar
WHERE IsWeekday = 1
AND Date >= @StartDate
AND Date <= @EndDate
ORDER BY NEWID();
You could always generate the dates on the fly though:
SET DATEFIRST 1;
DECLARE @Start DATE = '20160207',
@End DATE = '20160214';
WITH Calendar (Date) AS
( SELECT TOP (DATEDIFF(DAY, @Start, @End) + 1)
DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY N1.N) - 1, @Start)
FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n1 (N)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n2 (N)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n3 (N)
)
SELECT TOP 1 Date
FROM Calendar
WHERE DATEPART(WEEKDAY, Date) NOT IN (6, 7)
ORDER BY NEWID();
Here the calendar CTE cross joins 3 table valued constructors to generate a maximum of 1,000 rows (10 x 10 x 10), then limits that to the number of days required using
TOP (DATEDIFF(DAY, @Start, @End) + 1)
Then generates a list of dates onward from the start by using ROW_NUMBER() to generate values from 1 to n. So the basic element is:
DECLARE @Start DATE = '20160207',
@End DATE = '20160214';
WITH Calendar (Date) AS
( SELECT TOP (DATEDIFF(DAY, @Start, @End) + 1)
DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY N1.N) - 1, @Start)
FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n1 (N)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n2 (N)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n3 (N)
)
SELECT Date
FROM Calendar
Which gives:
Date
------------
2016-02-07
2016-02-08
2016-02-09
2016-02-10
2016-02-11
2016-02-12
2016-02-13
It is then a simple case of removing weekends with WHERE DATEPART(WEEKDAY, Date) NOT IN (6, 7) and selecting a random row with TOP 1 ... ORDER BY NEWID(). As as aside, when using something setting sensitive like DATEPART(WEEKDAY, ...) you should always explicitly set the value you need rather than relying on defaults.
I may have misunderstood your requirements though, this last step is not necessary if you just want a list of all the dates
A Calendar or Tally table would do the trick, but I often use a TVF to create dynamic date/time ranges. It is parameter driven, you define the Date/Time Range, DatePart and Increment
Example
Declare @D1 date = '2016-02-07'
Declare @D2 date = '2016-02-14'
Select Top 1 D=RetVal
From [dbo].[udf-Range-Date](@D1,@D2,'DD',1)
Where DatePart(DW,RetVal) not in (7,1)
Order By NewID()
Returns
D
2016-02-11 00:00:00.000
The UDF if Interested
CREATE FUNCTION [dbo].[udf-Range-Date] (@R1 datetime,@R2 datetime,@Part varchar(10),@Incr int)
Returns Table
Return (
with cte0(M) As (Select 1+Case @Part When 'YY' then DateDiff(YY,@R1,@R2)/@Incr When 'QQ' then DateDiff(QQ,@R1,@R2)/@Incr When 'MM' then DateDiff(MM,@R1,@R2)/@Incr When 'WK' then DateDiff(WK,@R1,@R2)/@Incr When 'DD' then DateDiff(DD,@R1,@R2)/@Incr When 'HH' then DateDiff(HH,@R1,@R2)/@Incr When 'MI' then DateDiff(MI,@R1,@R2)/@Incr When 'SS' then DateDiff(SS,@R1,@R2)/@Incr End),
cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
cte2(N) As (Select Top (Select M from cte0) Row_Number() over (Order By (Select NULL)) From cte1 a, cte1 b, cte1 c, cte1 d, cte1 e, cte1 f, cte1 g, cte1 h ),
cte3(N,D) As (Select 0,@R1 Union All Select N,Case @Part When 'YY' then DateAdd(YY, N*@Incr, @R1) When 'QQ' then DateAdd(QQ, N*@Incr, @R1) When 'MM' then DateAdd(MM, N*@Incr, @R1) When 'WK' then DateAdd(WK, N*@Incr, @R1) When 'DD' then DateAdd(DD, N*@Incr, @R1) When 'HH' then DateAdd(HH, N*@Incr, @R1) When 'MI' then DateAdd(MI, N*@Incr, @R1) When 'SS' then DateAdd(SS, N*@Incr, @R1) End From cte2 )
Select RetSeq = N+1
,RetVal = D
From cte3,cte0
Where D<=@R2
)
/*
Max 100 million observations -- Date Parts YY QQ MM WK DD HH MI SS
Syntax:
Select * from [dbo].[udf-Range-Date]('2016-10-01','2020-10-01','YY',1)
Select * from [dbo].[udf-Range-Date]('2016-01-01','2017-01-01','MM',1)
This will do the trick. I have used Date & Convert Functions.
DECLARE @i INT = 0
WHILE (@i<=7)
BEGIN
DECLARE @T TABLE
(
[DATE] DATE
)
INSERT INTO @T SELECT CONVERT (DATE,DATEADD(DAY,@i,'2016-02-07'))
SET @i = @i+1
IF(CONVERT (DATE,DATEADD(DAY,@i,'2016-02-07')) ='2016-02-11' OR CONVERT (DATE,DATEADD(DAY,@i,'2016-02-07')) ='2016-02-12')
SET @i = @i+2
END
SELECT * FROM @T
GO
Declare @i as int
Declare @dr as date
Set @i = 1
while @i < 8
Begin
SELECT @dr = DATEADD(DAY, @i, '2016-02-07')
while (((DATEPART(dw, @dr) + @@DATEFIRST) % 7) NOT IN (5, 6))
begin
print @dr
break;
end
Set @i = @i +1
End
go
How about something like this:
SELECT inp.d AS [Start_Date]
,incr.r AS Increment
,DATEADD(DAY, incr.r, inp.d) AS New_Date
,FORMAT(DATEADD(DAY, incr.r, inp.d), 'ddd') AS New_Date_Weekday
FROM (
-- Input date goes here
SELECT CAST('2016-02-07' AS DATE) AS d
) inp
CROSS JOIN
(
-- Generate 7 rows
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) - 1 AS r
FROM (VALUES(1),(1),(1),(1),(1),(1),(1)) v(n)
) incr
-- Convert week-day to 'Monday = 1 and Sunday = 7'-base;
-- Select a random week-day between 1 and 5
WHERE 1 + ( 5 + DATEPART(DW, DATEADD(DAY, incr.r, inp.d)) + @@DATEFIRST) % 7 = 1 + CAST(RAND() * 5 AS INT)
Thanks to Kakkarot for his answer explaining how to convert week-day count to be based on Monday = 1 and Sunday = 7.
