SP problem with dates 4

[ianholmes]

I need an SP to pick up today’s date, and then return the last 6 months values.

E g if today is March 1st,
then return 09, 10, 11, 12, 01 & 02

I can declare @mon1 numeric
@mon2 numeric
…… @mon6 numeric

But then I have tried DATEDIFF, but cannot get the syntax.


I use SQL V 8.00.194

 

[FlaBusInt]

I think you want to use DateAdd instead of DateDiff:

select
   Month(DateAdd(mm,-6,getdate()))
   Month(DateAdd(mm,-5,getdate()))
   Month(DateAdd(mm,-4,getdate()))
   Month(DateAdd(mm,-3,getdate()))
   Month(DateAdd(mm,-2,getdate()))
   Month(DateAdd(mm,-1,getdate()))

I suppose if you want to get fancy you could put the decrement in a loop....

 

[FlaBusInt]

Ooops, forgot the commas:

select
   Month(DateAdd(mm,-6,getdate())),
   Month(DateAdd(mm,-5,getdate())),
   Month(DateAdd(mm,-4,getdate())),
   Month(DateAdd(mm,-3,getdate())),
   Month(DateAdd(mm,-2,getdate())),
   Month(DateAdd(mm,-1,getdate()))

 

[ca8msm]

You could also use a loop e.g.

CREATE PROCEDURE MonthTest AS

DECLARE @MONTHS varchar(50)
DECLARE @i int
SET @i = 6
SET @MONTHS = ''

WHILE @i > 0
 BEGIN
  SET @MONTHS = @MONTHS + CAST(DATEPART(mm,DATEADD(mm,-@i,GetDate())) AS VARCHAR) + ', '
  SET @i = @i - 1
 END

SELECT LEFT(@MONTHS, LEN(@MONTHS)-1) AS Months

____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[ianholmes]

Thanks guys
Further complications
I now need the year as well as the month
(as separate variables )
ie 0609, 0610, 0611, 0612, 0701, and 0702
Many thanks for your continuing help.
I am new to this game.

 

[LNBruno]

select
right(convert(varchar, year(DateAdd(mm,-6,getdate()))),2) + convert(varchar,(Month(DateAdd(mm,-6,getdate())))),
right(convert(varchar, year(DateAdd(mm,-5,getdate()))),2) + convert(varchar,(Month(DateAdd(mm,-5,getdate())))),
right(convert(varchar, year(DateAdd(mm,-4,getdate()))),2) + convert(varchar,(Month(DateAdd(mm,-4,getdate())))),
right(convert(varchar, year(DateAdd(mm,-3,getdate()))),2) + convert(varchar,(Month(DateAdd(mm,-3,getdate())))),
right(convert(varchar, year(DateAdd(mm,-2,getdate()))),2) + convert(varchar,(Month(DateAdd(mm,-2,getdate())))),
right(convert(varchar, year(DateAdd(mm,-1,getdate()))),2) + convert(varchar,(Month(DateAdd(mm,-1,getdate()))))


< M!ke >

 

[AlexCuse]

Mike - slight change to your code (to account for the 1 digit months, you need to pad with a 0). I only did it for one month, because the paretheses make my brain want to explode.

[COLOR=blue]declare[/color] @n [COLOR=blue]int[/color]
[COLOR=blue]set[/color] @n = -3

[COLOR=blue]select[/color] [COLOR=#FF00FF]right[/color]([COLOR=#FF00FF]cast[/color]([COLOR=#FF00FF]year[/color]([COLOR=#FF00FF]dateadd[/color]([COLOR=#FF00FF]month[/color], @n, [COLOR=#FF00FF]getdate[/color]())) [COLOR=blue]as[/color] [COLOR=blue]varchar[/color]), 2) +
[COLOR=#FF00FF]right[/color]([COLOR=red]'0'[/color] + [COLOR=#FF00FF]cast[/color]([COLOR=#FF00FF]month[/color]([COLOR=#FF00FF]dateadd[/color]([COLOR=#FF00FF]month[/color], @n, [COLOR=#FF00FF]getdate[/color]())) [COLOR=blue]as[/color] [COLOR=blue]varchar[/color]), 2)

(used CAST to make it a little shorter and less painful

Hope it helps,

Alex


Ignorance of certain subjects is a great part of wisdom

 

[LNBruno]

Good catch, Alex! Have a PPT (purple pointy thingy)!

< M!ke >

 

[ianholmes]

Hi Alex

I get 701 for January 2007, and not 0701.
I tried inserting the '0' + for year, as below, but I still get the leading zero being dropped.

select @mon1 = (select right('0' + cast(year(dateadd(month, @n, getdate())) as varchar), 2) + right('0' + cast(month(dateadd(month, @n, getdate())) as varchar), 4))
select @mon1

Many thanks for your help guys.

 

[gmmastros]

I can declare @mon1 numeric
@mon2 numeric
…… @mon6 numeric

There's your problem. numbers cannot store leading 0's. Change the data type t0 varchar(4) instead.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

Hm, when I run the exact query you posted (declaring @mon1 as varchar(4) or char(4) I get 0701. How do you have it declared? As an int? If its' declared as an int, you lose the leading zero's.

I don't think you should need a variable to store that in either (sorry if this was just for testing). Can you post a bit more of your query?

Also, it helps to use [ignore]

here is my code...

[/ignore]

This shows the result:

here is my code...

Which is much easier to read

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ianholmes]

Thanks guys

varchar(4) works beutifully.

Many thanks