Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query to get Last month's Data Expand / Collapse
Author
Message
Posted Monday, March 16, 2009 7:07 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, May 20, 2016 4:28 AM
Points: 917, Visits: 2,307
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

Post #677093
Posted Monday, March 16, 2009 7:25 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:04 AM
Points: 40,524, Visits: 37,752
WHERE yourdatecolumn >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0)
AND yourdatecolumn < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)


--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."

Helpful Links:
How to post code problems
How to post performance problems
Post #677101
Posted Monday, March 16, 2009 7:26 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:04 AM
Points: 40,524, Visits: 37,752
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."

Helpful Links:
How to post code problems
How to post performance problems
Post #677102
Posted Monday, March 16, 2009 7:35 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, May 20, 2016 4:28 AM
Points: 917, Visits: 2,307
wow...Thank you very much for your quick reply....This is perfect...and Thanks a lot for your valuable suggestion ...I got it....
Post #677112
Posted Monday, March 16, 2009 9:29 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:04 AM
Points: 40,524, Visits: 37,752
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."

Helpful Links:
How to post code problems
How to post performance problems
Post #677144
Posted Friday, March 21, 2014 12:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 10, 2016 11:23 AM
Points: 5, Visits: 94
That helps..
Thanks a lot..
Post #1553635
Posted Friday, March 21, 2014 2:28 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:04 AM
Points: 40,524, Visits: 37,752
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."

Helpful Links:
How to post code problems
How to post performance problems
Post #1553666
Posted Wednesday, April 20, 2016 9:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 20, 2016 9:43 AM
Points: 9, Visits: 64
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



Post #1779446
Posted Wednesday, April 20, 2016 9:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:28 AM
Points: 2,088, Visits: 6,179
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!
Post #1779459
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse