可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效,请关闭广告屏蔽插件后再试):
问题:
I want to get all dates by declaring month and year in SQL server.
Can anyone please share easy lines of SQL code to get it.
For example:
DECLARE @month AS INT = 5
DECLARE @Year AS INT = 2016
SELECT * from Something
I have tried below things,
DECLARE @month TINYINT=5
;WITH CTE_Days AS (
SELECT DATEADD(
MONTH,
@month,
DATEADD(
MONTH,
-MONTH(GETDATE()),
DATEADD(
DAY,
-DAY(GETDATE()) + 1,
CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
)
)
) Dates
UNION ALL
SELECT DATEADD(DAY, 1, Dates)
FROM CTE_Days
WHERE Dates < DATEADD(
DAY,
-1,
DATEADD(
MONTH,
1,
DATEADD(
MONTH,
@month,
DATEADD(
MONTH,
-MONTH(GETDATE()),
DATEADD(
DAY,
-DAY(GETDATE()) + 1,
CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
)
)
)
)
)
)
SELECT Dates
FROM CTE_Days
But I am looking for easy solution with less lines and short answer
回答1:
You can't get all days just by declaring the month, you need to add the year as well because of leap years:
DECLARE @date DATE = getdate()
;WITH N(N)AS
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a)
SELECT top(day(EOMONTH(@date)))
N day,
dateadd(d,N-1, @date) date
FROM tally
Another different solution(by t@clausen):
DECLARE @month AS INT = 5
DECLARE @Year AS INT = 2016
;WITH N(N)AS
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a)
SELECT N day,datefromparts(@year,@month,N) date FROM tally
WHERE N <= day(EOMONTH(datefromparts(@year,@month,1)))
回答2:
Same approach as t-clausen, but a more compact:
Declare @year int = 2017, @month int = 11;
WITH numbers
as
(
Select 1 as value
UNion ALL
Select value + 1 from numbers
where value + 1 <= Day(EOMONTH(datefromparts(@year,@month,1)))
)
SELECT datefromparts(@year,@month,numbers.value) Datum FROM numbers
回答3:
If you have a date/time column, then use the month()
function:
select t.*
from t
where month(datecol) = 5;
回答4:
You can get all the dates of a month using the following query.
declare @month int, @year int
set @month = 2
set @year = 2008
SELECT
CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) + Number
FROM master..spt_values
WHERE type = 'P'
AND
(CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) + Number )
<
DATEADD(mm,1,CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) )
Hope this will help you.
回答5:
Another CTE based version
DECLARE @Month INT = 2, @Year INT = 2019
;WITH MonthDays_CTE(DayNum)
AS
(
SELECT DATEFROMPARTS(@Year, @Month, 1) AS DayNum
UNION ALL
SELECT DATEADD(DAY, 1, DayNum)
FROM MonthDays_CTE
WHERE DayNum < EOMONTH(DATEFROMPARTS(@Year, @Month, 1))
)
SELECT *
FROM MonthDays_CTE
回答6:
DECLARE @MonthNo TINYINT = 03 -- set your month
,@WOYEAR SMALLINT = 2018; -- set your year
IF OBJECT_ID('TEMPDB..#TMP') IS NOT NULL
DROP TABLE #TMP
DECLARE @START_DATE DATETIME
,@END_DATE DATETIME
,@CUR_DATE DATETIME
SET @START_DATE = DATEADD(month, @MonthNo - 1, DATEADD(year, @WOYEAR - 1900, 0))
SET @END_DATE = DATEADD(day, - 1, DATEADD(month, @MonthNo, DATEADD(year, @WOYEAR - 1900, 0)))
SET @CUR_DATE = @START_DATE
CREATE TABLE #TMP (
WEEKDAY VARCHAR(10)
,DATE INT
,MONTH VARCHAR(10)
,YEAR INT
,dates VARCHAR(25)
)
WHILE @CUR_DATE <= @END_DATE
BEGIN
INSERT INTO #TMP
SELECT DATENAME(DW, @CUR_DATE)
,DATEPART(DAY, @CUR_DATE)
,DATENAME(MONTH, @CUR_DATE)
,DATEPART(YEAR, @CUR_DATE)
,REPLACE(CONVERT(VARCHAR(9), @CUR_DATE, 6), ' ', '-')
SET @CUR_DATE = DATEADD(DD, 1, @CUR_DATE)
END
SELECT *
FROM #TMP
回答7:
DECLARE @Today DATE= GETDATE() ,
@StartOfMonth DATE ,
@EndOfMonth DATE;
DECLARE @DateList TABLE ( DateLabel VARCHAR(10) );
SET @EndOfMonth = EOMONTH(GETDATE());
SET @StartOfMonth = DATEFROMPARTS(YEAR(@Today), MONTH(@Today), 1);
WHILE @StartOfMonth <= @EndOfMonth
BEGIN
INSERT INTO @DateList
VALUES ( @StartOfMonth );
SET @StartOfMonth = DATEADD(DAY, 1, @StartOfMonth);
END;
SELECT DateLabel
FROM @DateList;
回答8:
WHERE Dates LIKE '2018-12%'
In a datetime or timestamp it goes Year-Month, so this would pull everything that matches 2018 in December. You can mod this to use your variables as well.
@month = 12;
@year = 2018;
@searchQuery = @year + @month + '%';
WHERE Dates LIKE @searchQuery
回答9:
Little modification. Query given by @t-clausen.dk will give you correct result only if you ran it on first of the month. With little change this works awesome.
DECLARE @date DATE = getdate()
;WITH N(N)AS
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a)
SELECT top(day(EOMONTH(@date)))
N day,
DATEFROMPARTS(year(@date),month(@date), n) date
FROM tally
BTW nice trick t-clausen.dk. I couldn't think of more easy way