CODEWITHSUNDEEP
sqldate
Carlos Ferreira
Carlos Ferreira

Reputation: 13

SQL Query List of months between dates

I've been searching without success for a way to list the months where my tables entries are in use.

Let's say we have a table with items in use between two dates :

ID   StartDate   EndDate as ItemsInUse

A   01.01.2013   31.03.2013
B   01.02.2013   30.04.2013
C   01.05.2013   31.05.2013

I need a way to query that table and return something like :

ID  Month
A   01
A   02
A   03
B   02
B   03
B   04
C   05

I'm really stuck with this. Does anyone have any clues on doing this ?

PS : European dates formats ;-)

Upvotes: 1

Views: 5055

Answers (5)

PAA
PAA

Reputation: 11985

Here should be the answer:

select ID, 
       ROUND(MONTHS_BETWEEN('31.03.2013','01.01.2013')) "MONTHS" 
from TABLE_NAME;

Upvotes: 1

Martin Milan
Martin Milan

Reputation: 6390

Hmm... Dare say this can be improved on, but as a rough 'n' ready (assuming ms-sql)

create table #results (ID varchar(10), Month int, Year int);

declare @ID varchar(10);
declare @StartDate datetime;
declare @EndDate datetime;

declare myCursor cursor for select [ID], [StartDate],[EndDate] from ItemsInUse;
open myCursor;
delete from #results;
fetch next from myCursor into @ID, @StartDate, @EndDate;
declare @tempDate datetime;
 while @@FETCH_STATUS = 0
begin
    set @tempdate = CAST( CAST(year(@StartDate) AS varchar) + '-' + CAST(MONTH(@StartDate) AS varchar) + '-' + CAST(1 AS varchar) AS DATETIME);
    while @tempDate < @EndDate
    begin
        insert into #results values (@ID, MONTH(@tempDate), YEAR(@tempDate));
        set @tempDate = DATEADD(month,1,@tempdate);
    end

    fetch next from myCursor into @ID, @StartDate, @EndDate;
end

close myCursor;
deallocate myCursor;

select * from #results;
drop table #results;

Upvotes: 0

borrible
borrible

Reputation: 17356

If your dates are padded in the way you suggest - i.e. where one digit days or months have an initial 0 - you can just use the SUBSTRING function as in:

SELECT SUBSTRING(StartDate, 3, 2) ...

Alternatively, if you do not have the padding you can use:

SELECT SUBSTRING_INDEX( SUBSTRING_INDEX(StartDate, '.',2), '.', -1) ...

(This latter command is in two parts. The inner SUBSTRING_INDEX generates a string containing your day and month, and the outer SUBSTRING_INDEX takes the month.)

Upvotes: 0

michalzuber
michalzuber

Reputation: 5215

I would use MONTH() http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_month

SELECT ID, MONTH(StartDate)

Upvotes: 0

T I
T I

Reputation: 9933

Create a calendar table then

SELECT DISTINCT i.Id, c.Month
  FROM Calendar c
  JOIN ItemsInUse i 
    ON c.ShortDate BETWEEN i.StartDate AND i.EndDate

Upvotes: 2

Related Questions