Date difference Month wise

[sbaud2003]

Hi Sir/madam

I want to get the no of days for each Month between two dates , for eg From 23-Sep-23 to 03-Oct-23.
Result I want month wise i.e. Sept -8 Days Oct-3 days (Both days are inclusive)
is there any method , in query
Can any body help

 

[CJ_London]

for September
?dateadd("m",1,#23-sept-2023#)-day(#23-sept-2023#)-#23-sept-2023#+1
8

for October
?day(#03-oct-2023#)
3

edit: September calc only correct if there are same or more days in the following month (e.g. if date was 31-Oct then value is 0 and should be 1.

Revised formula
?dateadd("m",1,#31-Jan-2023#)-day(dateadd("m",1,#31-Jan-2023#))-#31-jan-2023#+1
1

?dateadd("m",1,#23-sep-2023#)-day(dateadd("m",1,#23-sep-2023#))-#23-sep-2023#+1
8

 

[ebs17]

PARAMETERS
   parStartDate Date,
   parEndDate Date
;
SELECT
   calMonth,
   calYear,
   COUNT(*) AS NumberDays
FROM
   tblCalendar
WHERE
   calDate BETWEEN parStartDate AND parEndDate
GROUP BY
   calMonth,
   calYear

Requirement: There is a calendar table. The first field, calDate, continuously contains the days of a sufficient period. Two further fields each contain the month or year from the date.

You write in bold. Does that mean that you are particularly important?

 

[sbaud2003]

PARAMETERS
   parStartDate Date,
   parEndDate Date
;
SELECT
   calMonth,
   calYear,
   COUNT(*) AS NumberDays
FROM
   tblCalendar
WHERE
   calDate BETWEEN parStartDate AND parEndDate
GROUP BY
   calMonth,
   calYear

Requirement: There is a calendar table. The first field, calDate, continuously contains the days of a sufficient period. Two further fields each contain the month or year from the date.

You write in bold. Does that mean that you are particularly important?

Yes i have done the tblcalendar, now how to use the SQL

 

[sbaud2003]

for September
?dateadd("m",1,#23-sept-2023#)-day(#23-sept-2023#)-#23-sept-2023#+1
8

for October
?day(#03-oct-2023#)
3

edit: September calc only correct if there are same or more days in the following month (e.g. if date was 31-Oct then value is 0 and should be 1.

Revised formula
?dateadd("m",1,#31-Jan-2023#)-day(dateadd("m",1,#31-Jan-2023#))-#31-jan-2023#+1
1

?dateadd("m",1,#23-sep-2023#)-day(dateadd("m",1,#23-sep-2023#))-#23-sep-2023#+1
8

i have two date field DT1 and DT2 which may lies in two different months

 

[CJ_London]

i have two date field DT1 and DT2 which may lies in two different months

Yes, that is what your example showed and that is the solution provided -,so what is your point ?

Are you saying your example is too simple and does not cover all scenarios?

 

[Josef P.]

If this is required for multiple records, you can modify the example from #3 a bit:

select
    T.id,
    C.calYear,
    C.calMonth,
    COUNT(*) AS DaysOfMonth
from
    YourTableWithStartAndEnd as T
    inner join
    tblCalendar as C ON C.calDate between T.StartDate and T.EndDate
Group By
   T.ID,
   C.calYear,
   C.calMonth