[Resolved] Get date, first/last for prev month

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 2005 Forums

Transact-SQL (2005)

[Resolved] Get date, first/last for prev month

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-07-27 : 16:44:26

I there an easy way to:

@DateFrom = get current Year/Month and then subtract 1 month (to get previous month) and then set the beginning date to first day in the previous month

@DateTo = get current Year/Month and then subtract 1 month (to get previous month) and then set the ending date being last day in the previous month.

Thank you.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-27 : 16:56:10

There are a bunch of ways, here is one:

@DateFrom =  DATEADD(MONTH, DATEDIFF(MONTH, 31, CURRENT_TIMESTAMP), 0)
@DateTo = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 1, CURRENT_TIMESTAMP), 0))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-27 : 16:56:14

[code]DECLARE @DateFrom DATETIME,
@DateTo DATETIME

SELECT @DateTo = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0),
@DateFrom = DATEADD(MONTH, -1, @DateTo)

SELECT @DateFrom,
@DateTo

SELECT *
FROM Table1
WHERE Col1 >= @DateFrom
AND Col1 < @DateTo[/code]

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

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-07-28 : 08:42:28

Thank you guys, both worked.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-28 : 09:33:31

Just be cautious using Lamprey's suggestion if the column you are filtering on contains time information other than 12am / "00:00:00".

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

snufse
Constraint Violating Yak Guru

469 Posts