Calculate Dates

I have a monthly report that i run manually currently. So, the report runs from the previous month of 27th to the current month of 26th.

For Example i'm manually using the following:

declare @StartDate datetime = '08/27/2011 00:00:00'
declare @EndDate datetime = '09/26/2011 23:59:59'

for the next month Its going to be:

declare @StartDate datetime = '09/27/2011 00:00:00'
declare @EndDate datetime = '10/26/2011 23:59:59'

I w开发者_C百科anted to get the above results automatically. Please help.

Thanks.

Use

declare @EndDate datetime = DATEADD(month,1,@StartDate)

Then update your query to use

where [TheDate] >= @StartDate and [TheDate] < @EndDate

By using a less than operator, you won't need to worry about the time portion.

This should work:

declare @DayOfMonth tinyint set @DayOfMonth = 27
declare @Month tinyint set @Month = DATEPART(month, getDate())
declare @Year int set @Year = DATEPART(year, getDate())

declare @calcDate datetime
declare @startDate datetime
declare @endDate datetime

select @calcDate = 
    DATEADD(day, @DayOfMonth - 1, 
       DATEADD(month, @Month - 1, 
          DATEADD(Year, @Year-1900, 0)))
select @startDate = DATEADD(month, -1, @calcDate)
select @endDate = DATEADD(SECOND, -1, @calcDate)

select @startDate
select @endDate

Declare @ReportStartDay int = 27;

declare @CurrentDay int;
declare @dateDiff int = 0;
set @CurrentDay = DAY(DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())))
set @dateDiff = (@CurrentDay - @ReportStartDay) *-1;

Declare @ReportStartDate datetime = DATEADD(mm, -1, DATEADD(dd, @datediff, DATEDIFF(dd, 0, GETDATE())))
Declare @ReportEndDate datetime = DATEADD(dd, @dateDiff - 1, DATEDIFF(dd, 0, GETDATE()))

select @ReportStartDate as 'Start Date', @ReportEndDate as 'End Date'

I have a feeling there is a better way to do this though...

27th of last month:

DATEADD(month,DATEDIFF(month,'20110201',CURRENT_TIMESTAMP),'20110127')

26th of this month:

DATEADD(month,DATEDIFF(month,'20110101',CURRENT_TIMESTAMP),'20110126')

Rather than trying to set the time portion to the last possible moment on the 26th, it would be far better to use a less than < comparison, rather than <= or between. Then, you just need the 27th of this month:

DATEADD(month,DATEDIFF(month,'20110101',CURRENT_TIMESTAMP),'20110127')

If you want something based on a particular "base date" (rather than "this month"), then substitute that date value where I'm using CURRENT_TIMESTAMP. You always leave the date literals (e.g. '20110101') as they are.

All of the above calculations work in the same way - the inner DATEDIFF computes the number of month transitions that have occurred since some arbitrary date; we then add that same number of month transitions to a second date - not quite as arbitrary - that exhibits the final "offset" that we want to achieve (e.g. in the first one, the "arbitrary date" is 1st Feb 2011. The second date is 27th Jan 2011 - the 27th of the month before).

Create the dates like this:

declare @StartDate datetime 
declare @EndDate datetime 
set @EndDate = convert(datetime, cast(year(getdate()) as varchar) + '/' + cast(month(getdate()) as varchar) + '/' + '27', 101)
set @StartDate = dateadd(m, -1, @EndDate)

And then use them like this:

select ....
from ...
where MyDate >= @StartDate and MyDate < @EndDate