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

 

[Pat Hartman]

Since you want to return multiple values, you need to use a code loop and put it all inside a function. The function can then return the string you need. You pass it two arguments. The start date and the end date. Don't forget your code needs to identify leap years so that February will be correct.

 

[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