Divide given date into days
I am writing a Stored Procedure. Basically I need the yearly and monthly information from the user and do something. But my problem is that I didn't find that a given month has how many days. Let me explain with an example:
The user gives me the year and month as I said;
@year = 2015
@month = 07
So, I have to create lines like:
2015-07-01 2015-07-02 ..... 2015-07-31
My plan is adding these rows one by one to another temporary table.
The last status of my SP:
ALTER PROCEDURE [dbo].[Usp_CreateStats]
(
@Year VARCHAR (40),
@Month VARCHAR (40)
)
AS
BEGIN
DECLARE @StartTime VARCHAR (10)
DECLARE @EndTime VARCHAR (10)
SET @StartTime = '00:00:00'
SET @EndTime = '23:59:59'
DECLARE @Peoples TABLE (name VARCHAR(50))
INSERT INTO @Peoples (name) select distinct name from USERINFO
DECLARE @Dates TABLE (date VARCHAR(50))
END
I hope I explained correctly.
source to share
I changed the year and month variables to datatype int
, date column name in dates_date
and used convert
and while loop with dateadd
to populate the table @dates
:
ALTER PROCEDURE [dbo].[Usp_CreateStats]
(
@Year int,
@Month int
)
AS
BEGIN
DECLARE @StartTime VARCHAR (10),
@EndTime VARCHAR (10),
@Date date
SET @StartTime = '00:00:00'
SET @EndTime = '23:59:59'
SET @Date = CONVERT(date, right('0000' + cast(@year as char(4)), 4) + right('00' + cast(@month as char(2)), 2)+ '01', 112)
DECLARE @Peoples TABLE (name VARCHAR(50))
INSERT INTO @Peoples (name) select distinct name from USERINFO
DECLARE @Dates TABLE (dates_date date)
while month(@date) = @Month
begin
insert into @Dates (dates_date) values (@Date)
set @Date = dateadd(day, 1, @date)
end
END
source to share
To create dates based on @year
and @month
, you can use Tally Table .
DECLARE @year INT = 2015,
@month INT = 7
DECLARE @startDate DATE = DATEADD(MONTH, @month - 1, DATEADD(YEAR, @year - 1900, CAST('19000101' AS DATE)))
;WITH Tally (n) AS
( -- 100 rows
SELECT TOP(31)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
)
SELECT
DATEADD(DAY, N-1, @startDate)
FROM Tally t
WHERE
DATEADD(DAY, N-1, @startDate) < DATEADD(MONTH, 1, @startDate)
Note. Use appropriate data types for your variables. In this case, @month
and @year
should be INT
.
source to share
You can use recursive CTE.
Declare @Year as varchar(5) = '2015'
Declare @Month as varchar(5) = '05'
Declare @DateOfMonth as DateTime = @Month + '-01-' + @Year
Declare @startdate datetime, @enddate datetime
SET @startdate = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@DateOfMonth),0))
SET @enddate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateOfMonth)+1,0))
SET @enddate = DATEADD(day,-1,@enddate)
;With DateSequence( Date ) as
(
Select @startdate as Date
union all
Select dateadd(day, 1, Date)
from DateSequence
where Date < @enddate
)
--select result
Select * from DateSequence option (MaxRecursion 1000)
source to share
DECLARE @Month int= 7
Declare @Year int = 2015
Declare @MonthStart date = CAST(@Year as varchar(4)) + Right('00' + Cast(@Month as varchar(2)), 2) + '01'
DECLARE @DaysInMonth int = (SELECT
datediff(day, dateadd(day, 1-day(@MonthStart), @MonthStart),
dateadd(month, 1, dateadd(day, 1-day(@MonthStart), @MonthStart))))
Declare @Dates TABLE (date date)
DECLARE @Counter int = 0
WHILE @Counter < @DaysInMonth
BEGIN
INSERT INTO @Dates SELECT DATEADD(Day, @Counter, @MonthStart)
SET @Counter = @Counter + 1
END
source to share
CTE you can use:
DECLARE @year INT = 2015,
@month INT = 7,
@first_day_of_month DATETIME,
@last_day_of_month DATETIME
select @first_day_of_month = cast(cast(@year as varchar(4))+'/'+cast(@month as varchar(2))+'/'+'01' as date)
select @last_day_of_month = dateadd(mm,datediff(mm,0,@first_day_of_month)+1,0)-1
;with mycte as
(
select @first_day_of_month as src
union all
select dateadd(dd,1,src) from mycte
where src < @last_day_of_month
)select * from mycte
source to share
Use a recursive CTE,
DECLARE @year VARCHAR(4) = '2015',
@month VARCHAR(2)= '02',
@date DATETIME
SET @date= CONVERT(DATETIME, @year + '-' + @month + '-01');
WITH cte
AS (SELECT @date AS dt
UNION ALL
SELECT Dateadd(dd, 1, dt) AS dt
FROM cte
WHERE dt < Dateadd(dd, -1, Dateadd(mm, Datediff(mm, 0, @date) + 1, 0)))
SELECT *
FROM cte
source to share
Here's another (faster) solution:
DECLARE @year INT = 2015,
@month INT = 7
DECLARE @start datetime = dateadd(m, (@year- 1900) * 12 + 7, -31)
SELECT TOP(datediff(d, @start, dateadd(m, 1, @start)))
dateadd(d, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1, @start)
FROM
(VALUES(0),(0),(0),(0),(0),(0)) a(n)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0)) b(n)
source to share