如何获取当前月份名称，在sql server中的两个日期之间的开始日期和结束日期 [英] how to get current month name, start date and end date between two dates in sql server

问题描述

create procedure dummy2
as
begin
declare @sDate datetime,
        @eDate datetime

select  @sDate = '2013-02-25',
        @eDate = '2013-03-25'

;with cte as (


  select  convert(datetime,left(convert(varchar,@sdate,112),6) + '01') startDate ,
       month(@sdate) n
  union all
  select dateadd(month,n,convert(datetime,convert(varchar,year(@sdate)) + '0101')) startDate,
        (n+1) n 

  from cte
  where n < month(@sdate) + datediff(month,@sdate,@edate)  
)

select CONVERT(varchar(20), DATENAME(MONTH, startdate))as Months, startdate, dateadd(day,-1,dateadd(month,1,startdate)) enddate
from cte
end

Months  startdate   enddate
February    2013-02-01 00:00:00.000     2013-02-28 00:00:00.000
March   2013-03-01 00:00:00.000 2013-03-31 00:00:00.000

我需要得到开始日期列表和结束日期，月份名称是两个日期，以上是我的查询，但结果是不正确的。我需要以下结果

hi i need to get the list of start date and end date ,month name bewteen two dates, above is my query but the result is not correct..i need the result like below

注意：开始日期和结束日期是我给出的，在那天之间，我记录开始日期和结束日期，月份名称的月份列表

月| startdate |结束

month | startdate | enddate

feburary | 2013-02-25 | 2013-02-28
march | 2013-03-01 | 2013-03-25

feburary | 2013-02-25 | 2013-02-28 march | 2013-03-01 | 2013-03-25

推荐答案

可能有助于您

declare @sDate datetime,
        @eDate datetime
select  @sDate = '2013-02-21',
        @eDate = '2013-04-25'
;WITH CTE_TEST AS (
    SELECT @sDate SDATE,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@sDate)+1,0)) EDATE
    UNION ALL
    SELECT  EDATE+1,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,1,SDATE))+1,0))
    FROM    CTE_TEST C WHERE DATEADD(MONTH,1,SDATE) < DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@eDate)+1,0))
)   
SELECT DATENAME(MONTH,SDATE) MNAME,SDATE,(CASE WHEN EDATE > @eDate THEN @eDate ELSE EDATE END) EDATE FROM CTE_TEST  

这篇关于如何获取当前月份名称，在sql server中的两个日期之间的开始日期和结束日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！