current and previous month - 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 2005 Forums

Transact-SQL (2005)

current and previous month

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-01-06 : 07:51:10

i want to see all data from the current and previous month (12/2009 and 01/2010)

SELECT * FROM TABLE_DER
WHERE [YEAR]>=2009 AND [Month]>=MONTH(GETDATE())-1

the problem is ]>=MONTH(GETDATE())-1 is 0

what can i do?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-06 : 08:02:22

MONTH(dateadd(mm,-1,getdate()))

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-06 : 08:38:04

SELECT * FROM TABLE_DER
WHERE dateadd(year,[year]-1900,dateadd(month,[Month]-1,0))>=dateadd(month,datediff(month,0,getdate())-1,0)

Madhivanan

Failing to plan is Planning to fail

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-01-06 : 09:12:02

DATEADD(dd,-DAY(GETDATE()),DATEADD(mm,-1,GETDATE()))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-06 : 09:27:49

quote:
Originally posted by inbs

DATEADD(dd,-DAY(GETDATE()),DATEADD(mm,-1,GETDATE()))

Is this correct?
Post the full code

Madhivanan

Failing to plan is Planning to fail

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-01-06 : 18:40:59

[CODE]
select *
from MyTable
where MyDate >= DateAdd(month, -1, DateAdd(month, DateDiff(month, 0, GetDate()), 0))
[/CODE]

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 00:45:38

quote:
Originally posted by Bustaz Kool

[CODE]
select *
from MyTable
where MyDate >= DateAdd(month, -1, DateAdd(month, DateDiff(month, 0, GetDate()), 0))
[/CODE]

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

Seems like OP doesnt have a single date field. The initial post involves only separate fields for Year and Month

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-07 : 01:35:44

quote:
Originally posted by visakh16

quote:
Originally posted by Bustaz Kool

[CODE]
select *
from MyTable
where MyDate >= DateAdd(month, -1, DateAdd(month, DateDiff(month, 0, GetDate()), 0))
[/CODE]

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

Seems like OP doesnt have a single date field. The initial post involves only separate fields for Year and Month

Yes it is.

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources