可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效,请关闭广告屏蔽插件后再试):
问题:
I am writing a Stored Procedure. Basically i need take year and month information from user and doing something. But my problem is i don't detect given month has how many days. Let me explain with example;
User give me year and month as i said;
@year = 2015
@month = 07
So i must create rows like;
2015-07-01
2015-07-02
.....
2015-07-31
My plan is adding these rows one by one to another temp table.
Last status of my SP is;
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 explained correctly.
回答1:
I've changed the year and month variables to int
data type,
the name of your date column to dates_date
, and used convert
and a while loop with dateadd
function to populate the @dates
table:
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
回答2:
To generate the dates based on @year
and @month
, you can use a 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)
SQL Fiddle
Note: Use the appropriate data type for your variables. In this case, @month
and @year
should be INT
.
回答3:
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)
回答4:
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
回答5:
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
回答6:
Use 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
回答7:
Here is another (faster) tally 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)
回答8:
convert(varchar,OrderDate,111)='2015-07-01'