CODEWITHSUNDEEP
sql
rakela
rakela

Reputation: 303

Get previous month of a given date sql

I had this:

select company
from alldata
where **{previous month of date("01.05.2011")}**
IN
(select month from alldata where SumNrSpot=0)

I need to find the previous month in the where clouse. Please, anybody can help?

Upvotes: 0

Views: 13090

Answers (6)

rakela
rakela

Reputation: 303

I did it this way:

select company
from alldata
where MONTH(`Mon`) -1
IN
(select MONTH(Mon) from alldata where SumNrSpot=0)

Thanks anyway, some of your responses was useful and appreciated.

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107806

For SQL Server

You can just use the function MONTH() to get a month from a date.
You can use DATEADD(month,-1,) to adjust a date by a month.
You should always use the format YYYYMMDD if you present dates to SQL Server in textual form.

where MONTH(DATEADD(month,-1,'20110501') IN

That's assuming the table column alldata.month contains a numeric value of the month from 1-12.

Upvotes: 1

David Adlington
David Adlington

Reputation: 666

Select
DATEPART(mm,(DATEADD(m,-1,CONVERT(DATETIME,'01.05.2011')))) AS PreviousMonth
IN 
(Select month from alldata where SumNrSpot=0)

Assuming your "Month" column is an integer value

Upvotes: 0

fancyPants
fancyPants

Reputation: 51928

In case you're using MySQL:

SELECT MONTH(DATE_SUB('2011-05-01', 1 MONTH))

in your case

select company
from alldata
where MONTH(DATE_SUB('2011-05-01', 1 MONTH))
IN
(select month from alldata where SumNrSpot=0)

Read more about both functions here.

Upvotes: 3

solaimuruganv
solaimuruganv

Reputation: 29807

extract month from given date and add -1 

select ( extract (month from now()) -1 ) as previous_month

Upvotes: 0

Habib
Habib

Reputation: 223392

You may use DateAdd() method with parameter, month, and -1 as value to get previous month.

Following will give you 

select DatePart(mm,DateAdd(m,-1,GetDate())) as PreviousMonth

For your query you can try:

select company 
from alldata
where datepart(mm,dateadd(m,-1,Convert(DateTime, Convert(DateTime,'01.05.2011',104))))
IN (select month from alldata where SumNrSpot=0)

Upvotes: 1

Related Questions