如何仅在SQL中使用月份和年份生成天数 [英] How to generate days using month and year only in SQL

问题描述

我正在使用sql server 2005，我需要在sql中使用Month和YEar生成几天只在sql中





我期待答案是

我对sql的输入是'03 / 2019'



预期输出： -

01-03-2019

02-03-2019

'

'

'

'

'

'



'

'31 - 03-2019

如何解决这个问题？



我尝试了什么：



我已经使用了开始日期和结束日期来生成日子

I'm using sql server 2005 and i need to generate days in sql using Month And YEar only in sql


I expect the answer is
My input to sql is '03/2019'

Expected Output:-
01-03-2019
02-03-2019
'
'
'
'
'
'

'
'31-03-2019
How to genarate this?

What I have tried:

I already used with start and end Date to generate days

declare @StartDate as datetime
declare @EndDate as datetime

set @StartDate='03/01/2019'
set @EndDate='03/06/2019'

;WITH    AllDays      
AS ( SELECT   @StartDate AS EntryDate, (CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) AS [issunday]      
   UNION ALL      
   SELECT   DATEADD(DAY, 1, EntryDate),(CASE WHEN DATENAME(dw,  DATEADD(DAY, 1, EntryDate)) = 'Sunday' THEN 1 ELSE 0 END)      
   FROM     AllDays      
   WHERE    EntryDate < @EndDate )      
SELECT EntryDate, [issunday] into #tempX   
FROM   AllDays OPTION (MAXRECURSION 0)  

Select * from #TempX

推荐答案

我不是每次都重新生成它，而是预生成它：两个包含两列的表：Month和Day，with一年中每一天的一行（第二个表是闰年）

那么这只是将日期分成@MONTH和@YEAR的情况，然后在正确的表上使用简单的SELECT ：

Instead of regenerating it each time, I'd pregenerate it: two tables containing two columns: Month and Day, with a row for each day of the year (the second table is for leap years)
Then it's just a case of splitting the date into @MONTH and @YEAR, then using a simple SELECT on the correct table:

SELECT Day + '-' + Month + '-' + @YEAR FROM MonthNonLeapYear WHERE Month=@Month

比循环或CTE和临时表更有效。

Much more efficient than loops or CTEs and temporary tables.

这篇关于如何仅在SQL中使用月份和年份生成天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！