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
 General SQL Server Forums
 New to SQL Server Programming
 get data for previous month

Author  Topic 

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2014-07-28 : 07:37:28
I need to get previous month data in the table based on current date.

in case of execution of each month, the data for previous month should come with date as between




create table TestDate
(Sno Int,
Name varchar(100),
DateofJoin datetime)



insert into TestDate values (1,'Raj', '2/21/2014')
insert into TestDate values (1,'Britto', '6/12/2014')
insert into TestDate values (1,'Kumar', '5/14/2014')
insert into TestDate values (1,'Selva', '6/27/2014')
insert into TestDate values (1,'Ravi', '5/2/2014')
insert into TestDate values (1,'Gopu', '6/2/2014')

/*

if I execute in month July ( ie: today)

select * from TestDate where dateofjoin between 1-june-2014 and 30-june-2014


Result

5 Ravi 2014-05-02 00:00:00.000
3 Kumar 2014-05-14 00:00:00.000

if I execute in month June

select * from TestDate where dateofjoin between 1-may-2014 and 30-may-2014

Result


6 Gopu 2014-06-02 00:00:00.000
2 Britto 2014-06-12 00:00:00.000
4 Selva 2014-06-27 00:00:00.000

/*



THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2014-07-28 : 07:43:52

select * from TestDate where dateofjoin between
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) and
DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com
Go to Top of Page
   

- Advertisement -