Prev: TRY CATCH still reports error in Query window
Next: diagnosing Service Broker problems - help
From: MarkS on 5 Jan 2007 05:59
I am trying to write some T-SQL which calulates the previous twelve periods
from the current date in the Format mm/yy and writes them to a Table. I've
tried using GetDate and the Convert Function but no success. Ideally the
records should be similar to the following:

01/2006
02/2006
....
12/2006

Any help would be appreciated
From: matturbanowski on 5 Jan 2007 06:40
If it's any help, I've found a function which writes out all the days
between two specified dates into a table. Maybe you could change this
code so it works for the last 12 months instead...

CREATE FUNCTION dbo.fnSeqDates
(
@LowDate DATETIME,
@HighDate DATETIME
)
RETURNS @Dates TABLE
(
SeqDate DATETIME
)
AS

BEGIN
DECLARE @Temp DATETIME

IF @LowDate > @HighDate
SELECT @Temp = @LowDate,
@LowDate = DATEADD(day, DATEDIFF(day, 0, @HighDate),
0),
@HighDate = DATEADD(day, DATEDIFF(day, 0, @Temp), 0)
ELSE
SELECT @LowDate = DATEADD(day, DATEDIFF(day, 0, @LowDate),
0),
@HighDate = DATEADD(day, DATEDIFF(day, 0, @HighDate),
0)

INSERT @Dates
(
SeqDate
)
VALUES (
@LowDate
)

WHILE @@ROWCOUNT > 0
INSERT @Dates
(
SeqDate
)
SELECT DATEADD(dd, n.Items, d.SeqDate)
FROM @Dates d
CROSS JOIN (
SELECT COUNT(SeqDate) Items
FROM @Dates
) n
WHERE DATEADD(dd, n.Items, d.SeqDate) <= @HighDate

RETURN
END

From: Tracy McKibben on 5 Jan 2007 08:14
MarkS wrote:
> I am trying to write some T-SQL which calulates the previous twelve periods
> from the current date in the Format mm/yy and writes them to a Table. I've
> tried using GetDate and the Convert Function but no success. Ideally the
> records should be similar to the following:
>
> 01/2006
> 02/2006
> ...
> 12/2006
>
> Any help would be appreciated


SELECT RIGHT('00' + CONVERT(VARCHAR(2), MonthNum), 2) + '/' +
CONVERT(CHAR(4), YearNum) AS Period
FROM
(
SELECT MONTH(DATEADD(month, -1, GETDATE())) AS MonthNum,
YEAR(DATEADD(month, -1, GETDATE())) AS YearNum
UNION
SELECT MONTH(DATEADD(month, -2, GETDATE())) AS MonthNum,
YEAR(DATEADD(month, -1, GETDATE())) AS YearNum
UNION
SELECT MONTH(DATEADD(month, -3, GETDATE())) AS MonthNum,
YEAR(DATEADD(month, -1, GETDATE())) AS YearNum
UNION
SELECT MONTH(DATEADD(month, -4, GETDATE())) AS MonthNum,
YEAR(DATEADD(month, -1, GETDATE())) AS YearNum
UNION
SELECT MONTH(DATEADD(month, -5, GETDATE())) AS MonthNum,
YEAR(DATEADD(month, -1, GETDATE())) AS YearNum
UNION
SELECT MONTH(DATEADD(month, -6, GETDATE())) AS MonthNum,
YEAR(DATEADD(month, -1, GETDATE())) AS YearNum
UNION
SELECT MONTH(DATEADD(month, -7, GETDATE())) AS MonthNum,
YEAR(DATEADD(month, -1, GETDATE())) AS YearNum
UNION
SELECT MONTH(DATEADD(month, -8, GETDATE())) AS MonthNum,
YEAR(DATEADD(month, -1, GETDATE())) AS YearNum
UNION
SELECT MONTH(DATEADD(month, -9, GETDATE())) AS MonthNum,
YEAR(DATEADD(month, -1, GETDATE())) AS YearNum
UNION
SELECT MONTH(DATEADD(month, -10, GETDATE())) AS MonthNum,
YEAR(DATEADD(month, -1, GETDATE())) AS YearNum
UNION
SELECT MONTH(DATEADD(month, -11, GETDATE())) AS MonthNum,
YEAR(DATEADD(month, -1, GETDATE())) AS YearNum
UNION
SELECT MONTH(DATEADD(month, -12, GETDATE())) AS MonthNum,
YEAR(DATEADD(month, -1, GETDATE())) AS YearNum
) DateParts




--
Tracy McKibben
MCDBA
http://www.realsqlguy.com
From: mladjo on 5 Jan 2007 09:08
Hi.

You should try something like this (two way which depends on date order):

declare @today datetime
declare @numOfMonth int
set @today = getdate()
set @numOfMonth = -1

while (@numOfMonth>=(-12))
begin
SELECT RIGHT(CONVERT(varchar(10), dateadd(mm,@numOfMonth, @today),103),7)
set @numOfMonth = @numOfMonth-1
end


OR


declare @today datetime
declare @numOfMonth int
set @today = getdate()
set @numOfMonth = -12

while (@numOfMonth<=(-1))
begin
SELECT RIGHT(CONVERT(varchar(10), dateadd(mm,@numOfMonth, @today),103),7)
set @numOfMonth = @numOfMonth+1
end




 | 
Pages: 1
Prev: TRY CATCH still reports error in Query window
Next: diagnosing Service Broker problems - help