CODEWITHSUNDEEP
sql-serverdatedatetime
yoges
yoges

Reputation: 29

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

note : the start date and end date is what i given ,in between that days i neee the list of months starting date and ending date, month name

month | startdate | enddate

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

Upvotes: 3

Views: 3402

Answers (3)

Red
Red

Reputation: 29

How about inserting all the results to a new table?

Upvotes: 0

Louis Young
Louis Young

Reputation: 1

In data warehousing we a use a table known as a date dimension, this helps with performance, calculation mistakes, and a single point of truth for anybody using a date in that database. You can of course add any data about dates you need, but for brevity's sake, I've only added the full date and the MonthName:

CREATE TABLE DimDate
(
    DateKey INT NOT NULL PRIMARY KEY CLUSTERED,
    DateFull DATETIME NOT NULL,
    DateMonthName VARCHAR(20) NOT NULL
);

Here's some sample data:

DateKey DateFull DateMonthName

20130201 2013-02-01 00:00:00.000 February

20130202 2013-02-02 00:00:00.000 February

Then your query becomes as simple as this:

SELECT
    Months = ddd.DateMonthName,
    startDate = MIN(ddd.DateFull),
    endDate = MAX(ddd.DateFull)
FROM
    dbo.DimDate ddd WITH(NOLOCK)
WHERE
    ddd.DateFull BETWEEN '2013-02-25' AND '2013-03-25'
GROUP BY
    ddd.DateMonthName;

Below is your updated dummy2 proc:

CREATE PROCEDURE dummy2
AS
BEGIN
    DECLARE
        @sDate  DATETIME,
        @eDate  DATETIME;

    SELECT
        @sDate = '2013-02-25',
        @eDate = '2013-03-25';

    SELECT
        Months = ddd.DateMonthName,
        startDate = MIN(ddd.DateFull),
        endDate = MAX(ddd.DateFull)
    FROM
        dbo.DimDate ddd WITH(NOLOCK)
    WHERE
        ddd.DateFull BETWEEN @sDate AND @eDate
    GROUP BY
        ddd.DateMonthName;
END;

Upvotes: 0

Abdul Rasheed
Abdul Rasheed

Reputation: 6719

This may be help you

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

Upvotes: 2

Related Questions