Wednesday, March 7, 2012

q; start and end date of previous month

I need start and end date of previous month, such as StartDate=07/01/2006 and
EndDate=07/31/2006 for currentdate 08/156/2006
How can I do this?The start date of the previous month is easy, use DATEADD to subtract one
month, then set the day to 1.
For the last date of the previous month, subtract the day plus 1 (e.g. 15 +
1) from the current date. That will take you to the last day of the previous
month.
RLF
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:52D53ED0-F5D6-40A0-9D25-F691A537F004@.microsoft.com...
>I need start and end date of previous month, such as StartDate=07/01/2006
>and
> EndDate=07/31/2006 for currentdate 08/156/2006
> How can I do this?
>|||select LastdayofPerviousMonth = convert(char(10),getdate() -
day(getdate()),101)
select FirstdayofPerviousMonth = convert(char(10),getdate() -
day(getdate()) - day(getdate() - day(getdate())) +1,101)
regards,
Saravanan
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:52D53ED0-F5D6-40A0-9D25-F691A537F004@.microsoft.com...
>I need start and end date of previous month, such as StartDate=07/01/2006
>and
> EndDate=07/31/2006 for currentdate 08/156/2006
> How can I do this?
>|||JIM.H. wrote:
> I need start and end date of previous month, such as StartDate=07/01/2006 and
> EndDate=07/31/2006 for currentdate 08/156/2006
> How can I do this?
>
DECLARE @.CurrentDate DATETIME
SELECT @.CurrentDate = GETDATE()
PRINT 'First day of previous month:'
PRINT DATEADD(DAY, DATEDIFF(DAY, 0, DATEADD(d, -1 * (DAY(DATEADD(d, -1 *
DAY(@.CurrentDate), @.CurrentDate)) - 1), DATEADD(d, -1 *
DAY(@.CurrentDate), @.CurrentDate))), 0)
PRINT 'Last day of previous month:'
PRINT DATEADD(DAY, DATEDIFF(DAY, 0, DATEADD(d, -1 * DAY(@.CurrentDate),
@.CurrentDate)), 0)
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Keep in mind that if any date fields contain the time, that the last day of
the month (without time) will exclude all rows entered on the last day of
the month.
For an entire months entries, it may be 'safer' to evaluate datetime fields
[>=] the first of the month (midnight) and [<] the first of the following
month (midnight).
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:52D53ED0-F5D6-40A0-9D25-F691A537F004@.microsoft.com...
>I need start and end date of previous month, such as StartDate=07/01/2006
>and
> EndDate=07/31/2006 for currentdate 08/156/2006
> How can I do this?
>

No comments:

Post a Comment