Is there a better way of doing this?

happygv · January 5th, 2004, 08:47 AM

Hi All there,

I just came across an issue that's related to extracting the data of previous month and manupulating it to generate a report. I think this is a general issue. So far I have been contacted by two of my friends/ex-colleagues, to get it solved this. So I thought, if there could be a definitive solution for this, it would help others too who have similar issues. Since we are into the new year, and this was not coded keeping the year change in mind, and they have told me that it suddenly does not return the previous month's data. When I looked in to it, I came to know the year change issue was not kept in mind and designed. So I have given this as a solution.

Not only that I thought I would share this with you all here, so as to get better opinion or solution for the same as this forum has got a lot many sql gurus. May be others have different and easy approach for the same. I am eagar to know that.

Please take a look at it in your leisure time and post your opinions if any.

The following code returns the start and end date/time of the Previous month, based on which the data can be extracted later, which is not included in this.

Eg: -
@startdate = '2003-12-01 00:00:00.000'
@enddate = '2003-12-31 23:59:59.999'

__________________________________________________ ___

Code:

declare @startdate varchar(25), @enddate varchar(25)
If (datepart(m,getdate()) = 1)
    Begin
        select @startdate=convert(varchar(4),datepart(yyyy,dateadd(yyyy,-1,getdate()))) + '-' + convert(varchar(2),datepart(mm,dateadd(mm,-1,getdate()))) + '-01 00:00:00.000'
        select @enddate=convert(varchar(4),datepart(yyyy,dateadd(yyyy,-1,getdate()))) + '-' + convert(varchar(2),datepart(mm,dateadd(mm,-1,getdate()))) + '-' + convert(varchar(2),datepart(dd,dateadd(dd,-datepart(dd,getdate()),getdate()))) + ' 23:59:59.999'
    End
Else
    Begin
        select @startdate=convert(varchar(4),datepart(yyyy,getdate())) + '-' + convert(varchar(2),datepart(mm,dateadd(mm,-1,getdate()))) + '-01 00:00:00.000'
        select @enddate=convert(varchar(4),datepart(yyyy,getdate())) + '-' + convert(varchar(2),datepart(mm,dateadd(mm,-1,getdate()))) + '-' + convert(varchar(2),datepart(dd,dateadd(dd,-datepart(dd,getdate()),getdate()))) + ' 23:59:59.999'
    End
select @startdate, @enddate

Thanks in advance for your time and effort on this.

-Vijay G

Jeff Mason · January 5th, 2004, 12:28 PM

I prefer to use the datetime datatype rather than representing the resultant dates as strings. I use a technique like this:

Code:

declare @TheDate datetime
set @TheDate=GetDate()

declare @StartDate datetime
declare @EndDate datetime

set @StartDate=cast(DateName(mm,@TheDate)+' 01 '+ DateName(yyyy,@TheDate) as datetime)
set @EndDate=DateAdd(ms,-3,@StartDate)
set @StartDate=cast(DateName(mm,@EndDate)+' 01 '+ DateName(yyyy,@EndDate) as datetime)

This will set @StartDate and @EndDate to the first and last day of the month prior to the value in @TheDate.

It works by first constructing a string representation of a datetime equal to midnight on the first day of the month of @Thedate, casting it to a datetime, then subtracting 3 milliseconds from that, giving the latest time of the day before, which is the last day of the previous month. If you didn't care about the time, then subtracting 1 day from that date gives the same thing without the time. @StartDate is then set to the first day of that month.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

happygv · April 8th, 2004, 09:47 PM

Sorry for the delay in replying to this.

This looks very simple and helped me a lot. I have modified my code to this.

Thanks a lot Jeff.

-Vijay G