data for previous month

[cards4]

Hello,
I have a SQL statement that gets information from the previous month. It is scheduled to run every first of the month. However, if necessary, the SQL will need to be re-run after the first of the month but still get the same information. Currently, I have a dateadd(month, -1, getdate()) and dateadd(day, -1, getdate()) declaration to set the first and last day of the previous month. What can I do to get the same information if the SQL is run after the first of the month? Thank you.

 

[SQLDenis]

--The first day and last day of any month just use a date for which month you want it to return
for example for February 2005 it would be

declare @d datetime
select @d ='20050214'
SELECT DATEADD(mm, DATEDIFF(mm, 0, @d)+0, 0) as FirstDay,
DATEADD(d,-1,DATEADD(mm, DATEDIFF(mm, 0, @d)+1, 0)) as LastDay

For September 2006 it would be
declare @d datetime
select @d ='20050914'
SELECT DATEADD(mm, DATEDIFF(mm, 0, @d)+0, 0) as FirstDay,
DATEADD(d,-1,DATEADD(mm, DATEDIFF(mm, 0, @d)+1, 0)) as LastDay



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[cards4]

Would this still work if it ran on the first of the month?

 

[SQLDenis]

well try it out
let's assume you run it now

declare @d datetime
select @d =dateadd(mm,-1,getdate())

SELECT DATEADD(mm, DATEDIFF(mm, 0, @d)+0, 0) as FirstDay,
DATEADD(d,-1,DATEADD(mm, DATEDIFF(mm, 0, @d)+1, 0)) as LastDay

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/