Date Scope Expansion - SQL Server Forums

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

SQL Server Development (2000)

Date Scope Expansion

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-10-13 : 06:37:40

Please make my day! Although it's already been made - make it ULTRA!

where created between DATEADD(m, DATEDIFF(m, 0, getdate()), 0)
and DATEADD(m, DATEDIFF(m, 0, getdate())+1, 0)-1

The above gives me first & last day of current month. How do I repeat a query for this month and then every month into the past & the future, supplying my own past and future cutoffs? With the CREATED datetime being the crucial field here - selecting the other fields where created is in the scope of what I'm asking here...

Thanks!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 06:41:48

[code]DECLARE @paramDate DATETIME

SET @paramDate = '20081006'

DECLARE @dateFrom DATETIME,
@dateTo DATETIME

SELECT @dateFrom = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @paramDate), '19000101'),
@dateTo = DATEADD(MONTH, DATEDIFF(MONTH, '18991231', @paramDate), '19000101')

SELECT *
FROM Table1
WHERE createdDate >= @dateFrom
AND createdDate < @dateTo[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources