Query to get Last month's Data

Question

Post reply

Query to get Last month's Data

ssismaddy

SSCarpal Tunnel

Points: 4850
More actions
March 16, 2009 at 7:07 pm

#129890

Hi...I have a requirement, A stored proc will be scheduled to run some day in a month. It has to get the data for the last month when ever it is scheduled to run. For example, the table name is tblTime and Column name is DateCol which is of Datetime datatype....now
If we execute it in March, we have to get data for Febraury....
select * from tblTime where dateCol between '2008-02-01' and '2008-02-28'....Is there anyway to get this dynamically?? any help would be appreciated.....Thank you

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

[font="Courier New"] WHERE yourdatecolumn >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0)

AND yourdatecolumn < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)[/font]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

Oh yeah..l. I forgot. Never use BETWEEN for date comparisons of this nature because you never know when someone is going to include a time in the column along with the date.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ssismaddy SSCarpal Tunnel Points: 4850 More actions · Answer 3

wow...Thank you very much for your quick reply....This is perfect...and Thanks a lot for your valuable suggestion ...I got it....

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

You bet... thanks for the feedback. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rderisala1 SSC Veteran Points: 257 More actions · Answer 5

rderisala1

SSC Veteran

Points: 257

March 21, 2014 at 12:44 pm

#1699213

That helps..

Thanks a lot..

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

rderisala1 (3/21/2014)
That helps..
Thanks a lot..

You bet. Thank you for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

hrothenb SSC-Addicted Points: 427 More actions · Answer 7

DECLARE @PreviousMonthStart DATETIME

DECLARE @PreviousMonthEnd DATETIME

SET @PreviousMonthStart = DATEADD(m,DATEDIFF(m,0,GETDATE())-1,0)

SET @PreviousMonthEnd = DATEADD(ms,-2,DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))

PRINT @PreviousMonthStart

PRINT @PreviousMonthEnd

SELECT * FROM MyTable

WHERE MyDate >= @PreviousMonthStart

AND MyDate < @PreviousMonthEnd

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 8

Careful, that won't be guaranteed to be correct if the data is ever changed to datetime2 with a higher precision than datetime.

Might as well use Jeff's, which will work regardless of the data type.

DECLARE @PreviousMonthStart DATETIME

DECLARE @PreviousMonthEnd DATETIME

SET @PreviousMonthStart = DATEADD(m,DATEDIFF(m,0,GETDATE())-1,0);

SET @PreviousMonthEnd = DATEADD(ms,-2,DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0));

PRINT @PreviousMonthStart;

PRINT @PreviousMonthEnd;

CREATE TABLE #MyTable (MyDate datetime2(3), some_string varchar(100));

INSERT INTO #MyTable VALUES

('20160301 12:39:10.898','This one is fine.'),

('20160331 23:59:59.999','What about me? Sniff sniff');

--This one is only guaranteed to return

--correct results for datetime

SELECT * FROM #MyTable

WHERE MyDate >= @PreviousMonthStart

AND MyDate < @PreviousMonthEnd;

--Jeff's will always work

SELECT * FROM #MyTable

WHERE MyDate >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0)

AND MyDate < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0);

DROP TABLE #MyTable;

Cheers!