show back months from current 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 2000 Forums

Transact-SQL (2000)

show back months from current month

Author

Topic

imughal
Posting Yak Master

192 Posts

Posted - 2004-07-08 : 06:00:11

hi,

i have to get list of back months from current month. for exp if i have selected Jul-2004 then result should display like that.

jan-2004
feb-2004
mar-2004
aprl-2004
may-2004
jun-2004

how i can get this list

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-08 : 06:08:51

Do you only need for the current year?

Hmm - lets see, this is based on a technique I read in an articale - not sure if it is the bestm but hey:


set nocount on

declare @datein datetime
set @datein = getdate()

create table #tempnum
(num smallint)

insert into #tempnum values (1)
insert into #tempnum values (2)
insert into #tempnum values (3)
insert into #tempnum values (4)
insert into #tempnum values (5)
insert into #tempnum values (6)
insert into #tempnum values (7)
insert into #tempnum values (8)
insert into #tempnum values (9)
insert into #tempnum values (10)
insert into #tempnum values (11)
insert into #tempnum values (12)

select dateadd(month,num*-1,@datein)
from #tempnum
where num < datepart(month,@datein)

drop table #tempnum

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

imughal
Posting Yak Master

192 Posts

Posted - 2004-07-12 : 05:50:13

hi,

thx is there any possibility to set year as user input.
not fix to current system year.

bye

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-12 : 11:52:39

sure .... but then you need to have code, or save it as Template.


set nocount on
declare @year int
declare @datein datetime

--press ctrl+shift+M, and enter a date
set @datein = '<@datein,datetime,value>'

create table #tempnum
(num smallint)

insert into #tempnum values (1)
insert into #tempnum values (2)
insert into #tempnum values (3)
insert into #tempnum values (4)
insert into #tempnum values (5)
insert into #tempnum values (6)
insert into #tempnum values (7)
insert into #tempnum values (8)
insert into #tempnum values (9)
insert into #tempnum values (10)
insert into #tempnum values (11)
insert into #tempnum values (12)

select dateadd(month,num*-1,'<@datein,datetime,value>')
from #tempnum
where num < datepart(month,'<@datein,datetime,value>')
drop table #tempnum

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

imughal
Posting Yak Master

192 Posts

Posted - 2004-07-13 : 01:26:29

thx it work fine when i press ctrl+shift+M , but how i can set this values programatically. i want to recieve this value as parameter from my user end form how i can assign it.

thx

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-13 : 03:55:44

Ok - then you need to have a piece of code calling this.

In that case, consider change it to a Stored procedure, which you then call from your code.



set nocount on
go
create procedure usp_prev_months 
  @datein datetime
as
BEGIN
if @datein is not null
  begin
  create table #tempnum
  (num smallint)

  insert into #tempnum values (1)
  insert into #tempnum values (2)
  insert into #tempnum values (3)
  insert into #tempnum values (4)
  insert into #tempnum values (5)
  insert into #tempnum values (6)
  insert into #tempnum values (7)
  insert into #tempnum values (8)
  insert into #tempnum values (9)
  insert into #tempnum values (10)
  insert into #tempnum values (11)
  insert into #tempnum values (12)

  select dateadd(month,num*-1,@datein)
  from #tempnum
  where num < datepart(month,@datein)
  drop table #tempnum
end
else
  select 'No date entered - please supply valid date'
end

--************ example usage **********
-- you need to create the datetime variable in your code
-- get the input value and exec the stored procedure

declare @date datetime 
--select @date = getdate()

exec usp_prev_months @date

hope that helps

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources