get last previous month records

Question

sql 2005 server

get previous month records

Date        product
24-05-2014  ball
25-05-2014  bat
01-06-2014  hat

i need

Date        Product
24-05-2014  ball
25-05-2014  bat

declare @ex datetime
set @ex '06-01-2014'

select * from tabl where DATENAME(m,DATEADD(m,0,Date)) =DATENAME(m, DATEADD(m,0, @ex))- it works
select * from tabl where DATENAME(m,DATEADD(m,0,Date)) =DATENAME(m, DATEADD(m,-1,@ex))-not works

@user3426968 I'm not sure that will work, because this is for SQL Server 2005, and that function doesn't exist. — podiluska, Jun 09 '14 at 13:36
Possible duplicate http://stackoverflow.com/q/1424999/1741542 — Olaf Dietsche, Jun 09 '14 at 13:39
This code works for me, how are you putting the dates into your temp table? Try just running SELECT DATENAME(m,DATEADD(m,0,ProdDate)) FROM tabl and see what the datenames are coming out as? — Kevin Cook, Jun 09 '14 at 13:45
DATENAME(m,DATEADD(m,0,ProdDate)) & DATENAME(m,DATEADD(m,+1,ProdDate)) both works but DATENAME(m,DATEADD(m,-1,ProdDate))does not work — Raj, Jun 09 '14 at 13:51
SqlFiddle link: http://sqlfiddle.com/#!6/ae57e/4 Not sure what error you are getting on your side. — Kevin Cook, Jun 09 '14 at 13:53
Are you using "Date" for a column name? It is a reserved word for a sql type, maybe confusion from that? — Kevin Cook, Jun 09 '14 at 13:55
I have a strong feeling that your dates aren't being interpreted the way you think they are, or the way you want them to be. — Mark C., Jun 09 '14 at 14:10

score 1 · Accepted Answer · answered Jun 09 '14 at 13:38

My sample code (tested on 2008). I don't know are YEAR and MOTH function in 2005 if not you need to use some string function to extract date / month part from datetime converted to string

declare @ex datetime = '2014-01-01'
declare @prev_year int
declare @prev_month int

set @prev_year = year(dateadd(month, -1, @ex))
set @prev_month = month(dateadd(month, -1, @ex))

select * from tabl 
where year(Date) = @prev_year and month(Date) = @prev_month

score 0 · Answer 2 · answered Jun 09 '14 at 14:20

You're using dd-MM-yyyy format for your dates, which is a varchar in SQL Server. Therefor, you must use CONVERT :

declare @ex varchar(10)
set @ex = '06-01-2014'

SELECT  DATENAME(m,DATEADD(m,0,GETDATE())),

 DATENAME(m, DATEADD(m,-1,CONVERT(date, @ex, 103)));

This yields results:

June | May

I think you can figure out your solution from here.

Note: If you use declare @ex datetime , your results will yield June | December

get last previous month records

2 Answers2