Declaring a date as being the last month day of another date

Question

I'm trying to write a financial report on our SAP B1 system using SQL Server Studio.

In my report I want the information to be calculated on a month to month basis. In my report I have @start date as @Startofcurrentfinancialyear, and my end as DD+30 (because there are 31 days in the month) However I am wanting to have mm+1 and dd-1 to bring me to the last day in the month.

I plan on changing the report for each month to give me the following. MM+1 (for month 2) and MM+2 - DD 1 to give me the date range for month 2 etc.

Currently, I can make this go based on the following: MM+0, DD+30, then going ahead doing DD+60 etc and calculating for each month how many days they are, but this will give me problems with leap years.

DECLARE @Start DATETIME = DATEADD(MM,-0,@StartOfCurrentFinancialYear)
DECLARE @End DATETIME = DATEADD(DD,+30,@StartOfCurrentFinancialYear)

I expect to be able to define a month for each section and give the last day of the defined month based on the parameters given above.

Answer 1

If you want the end of month, then in all supported versions of SQL Server, you can do:

DECLARE @Start DATETIME = DATEADD(MONTH, -0, @StartOfCurrentFinancialYear);
DECLARE @End DATETIME = EOMONTH(@StartOfCurrentFinancialYear);

If you are using an unsupported version, you can do date arithmetic:

DECLARE @End DATETIME = DATEADD(day, -1,
                                DATEADD(month, 1,
                                        DATEADD(day,
                                                1 - DAY(@StartOfCurrentFinancialYear), 
                                                @StartOfCurrentFinancialYear
                                               ),
                                       )
                                 );

This does the following calculation:

• Innermost subquery dateadd() moves to the first day of the month.
• Middle subquery adds one month.
• Outer query subtracts one day.