How to split the date into monthwise

Question

Using SQL Server 2000

I want to save the details in month wise

User Entry

ID = 001
Fromdate = 01/01/2012
Todate = 29/03/2012

ID = 002
Fromdate = 01/05/2012
Todate = 19/06/2012

ID = 003
Fromdate = 01/04/2012
Todate = 30/04/2012
.....

From the user entry, i want to insert the details in to table1 with the condition like fromdate and todate split in to monthwise (mm/yyyy)

Expected Output

ID period fromdate todate

001 01/2012 01/01/2012 31/01/2012
001 02/2012 01/02/2012 29/02/2012
001 03/2012 01/03/2012 29/03/2012
002 05/2012 01/05/2012 31/05/2012
002 05/2012 01/06/2012 19/06/2012
003 04/2012 01/04/2012 30/04/2012
....
....

fromdate, todate fomat is dd/mm/yyyy period format is mm/yyyy

How to do this in sql server.

Need sql Query Help

You can get a month with MONTH(fromdate) and year with YEAR(fromdate), but what you have tried so far? I expect you have to write a procedure or table function for this. — YvesR, May 29 '12 at 09:19
Can you please explain better. Do you want to store the details in a table and query from the table? — Muthukumar, May 29 '12 at 09:20
Is `Fromdate` always the first day of a month? Can it be (as in ‘should it be allowed to be’) a different day? — Andriy M, May 29 '12 at 10:24

score 1 · Accepted Answer · edited May 23 '17 at 12:26

1

First, create and populate a calendar table that has the following columns (at least):

CREATE TABLE dbo.Calendar (
    BaseDate datetime NOT NULL PRIMARY KEY,
    Period char(7) NOT NULL,
    YearNumber int NOT NULL,
    MonthNumber int NOT NULL,
    IsFirstDayOfMonth bit NOT NULL,
    IsLastDayOfMonth bit NOT NULL
)

Then this query will return the data you need, for one pair of fromdate and todate but you can of course put it into a stored procedure and call it once per user input or whatever way you execute SQL from your application:

declare @ID char(3), @FromDate datetime, @ToDate datetime

select @ID ='001', @FromDate = '20120107', @ToDate = '20120917'

select
    @ID as 'ID',
    c.Period,
    case when c.IsFirstDayOfMonth = 0x0 then @FromDate else c.BaseDate end as 'FromDate',
    case when @ToDate < c1.BaseDate then @ToDate else c1.BaseDate end as 'ToDate'
from
    dbo.Calendar c
    join dbo.Calendar c1
    on c.YearNumber = c1.YearNumber and c.MonthNumber = c1.MonthNumber
where
    c.BaseDate between @FromDate and @ToDate and
    (c.IsFirstDayOfMonth = 0x1 or c.BaseDate = @FromDate) and
    c1.IsLastDayOfMonth = 0x1

edited May 23 '17 at 12:26

Community

1
1

answered May 29 '12 at 14:02

Pondlife

15,992
6
37
51

What data's i have to enter in calendar table...?, i run your query, no error and also no output.... – JetJack Jun 02 '12 at 06:43
@JetJack You populate the table with whatever date range and set of columns you want. You can see a complete example [here](http://weblogs.sqlteam.com/dang/archive/2010/07/19/calendar-table-and-datetime-functions.aspx) although you personally have to decide what columns make sense for your queries. It's easy to add more columns in future, so don't feel that you have to get it right first time. If you create the table I describe and populate it accordingly then the query will work. – Pondlife Jun 02 '12 at 08:10
@JetJack I did show you one example, in the article I linked to. You've asked a lot of questions about date handling and formatting based on the same table and data set and received a lot of useful answers. But you're still asking questions that - in my opinion - are fairly similar. I don't want to be harsh or unhelpful, but please take some time to read and understand all the answers, all the relevant documentation and all the links people have provided. Then your increased understanding will allow you to ask more precise questions that in turn get better answers. – Pondlife Jun 02 '12 at 13:42

score 1 · Answer 2 · edited May 23 '17 at 10:09

You could use a numbers table (have you got one yet?) to generate the months:

SELECT
  ID,
  Period   = RIGHT(CONVERT(varchar(10), MonthStart, 103), 7),
  /*
  alternatively:
  Period = RIGHT('0' + RTRIM(MONTH(MonthStart), 2) + '/' + RTRIM(YEAR(MonthStart)),
  */
  FromDate = CASE WHEN RangeStart > MonthStart THEN RangeStart ELSE MonthStart END,
  ToDate   = CASE WHEN RangeEnd   < MonthEnd   THEN RangeEnd   ELSE MonthEnd   END
FROM (
  SELECT
    ID,
    RangeStart,
    RangeEnd,
    Monthstart,
    MonthEnd = DATEADD(DAY, -1, DATEADD(MONTH, 1, Monthstart))
  FROM (
    SELECT
      ID         = @ID,
      RangeStart = @RangeStart,
      RangeEnd   = @RangeEnd,
      MonthStart = DATEADD(MONTH, DATEDIFF(MONTH, 0, @RangeStart), 0)
    FROM numbers
    WHERE Number BETWEEN 0 AND DATEDIFF(MONTH, @RnageStart, @RangeEnd)
  ) m
) m

As a temporary substitute for the numbers table, you could use the master..spt_values system table, or, more exactly, its particular subset where type = 'P'. So, you can just replace the FROM numbers above with this subselect:

FROM (
  SELECT Number
  FROM master..spt_values
  WHERE type = 'P'
) numbers

How to split the date into monthwise

2 Answers2

Linked