Calculating the PREVIOUS month's date range.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Calculating the PREVIOUS month's date range.

Author

Topic

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-08-02 : 10:13:34

Hello all...

I have a question regarding a query I run at the beginning of each month and I'm trying to automate the whole thing into a stored procedure. I'm getting stuck when it comes to calculating the Previous months date range.

For example, today (8/2/02) I would like to run a report for all of July-02 so I would need to specify a BETWEEN in my where clause like so:

Select [xDate] from xTable
where [xDate] between '07/01/02' and '07/31/02'

How can I automatically calulate the beginning and ending dates for the Previous Month?

thanks.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-08-02 : 10:23:25

you need to include some form of "Dateadd" (with a negative offset)
BOL or a search here will show you some working examples....

how large the offset needs to be is key....31 days will always shove you back at least 1 month...problem is sometimes it may shove you back 2 months....

joshb
Yak Posting Veteran

52 Posts

Posted - 2002-08-02 : 10:24:16

You don't necessarily have to, something like this would do the trick:

SELECT xDate
FROM xTable
WHERE DATEPART(yy, xDate) = DATEPART(yy, GETDATE())
AND DATEPART(mm, xDate) = DATEPART(mm, GETDATE)))

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-02 : 10:35:18

Here's an elaboration on Andrew's suggestion:

SELECT xDate FROM xTable
WHERE xDate BETWEEN DateAdd(mm, -1, GetDate())-Day(GetDate()) + 1 AND
GetDate()-Day(GetDate()) + 1

The only problem with this is that GetDate() also returns the time, so you'd have to CONVERT() GetDate() to a date-only string format and then back to a date again.

This might perform better:

SELECT xDate FROM xTable
WHERE DateDiff(month, xDate, GetDate()) = -1

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-08-02 : 10:37:34

You da man!

thanks....

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-08-02 : 10:57:46

No mess, no fuss, no BETWEEN

WHERE xDate >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)
AND xDate < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)

MikeHulen
Starting Member

2 Posts

Posted - 2002-08-02 : 11:00:16

In appreciation for the help I recieved on my SQL Mail question in other forum:

Here's a way to always pin the date range regardless of how many days are in the month.

Create sp_getData
@inputdate datetime
AS

select xdate from xtable
where xdate between
dateadd(month,-1,cast(datepart(mm,@inputdate) as varchar) + '/01/' + cast(datepart(yy,@inputdate)as varchar))
and
dateadd(day,-1,cast(datepart(mm,@inputdate) as varchar) + '/01/' + cast(datepart(yy,@inputdate)as varchar))

There may be simpler, more elegant ways to do this, but I use techniques very similar to this in a lot of my code for mining financial data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources