A possible way is to have a table with twelve numbers in it
CREATE TABLE TwelveNumbers (
number INT PRIMARY KEY
);
Add the numbers 1 ... 12 to this table. Then you can do
INSERT INTO mytable (columnname)
SELECT DATEADD(month, -TwelveNumbers.number, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
FROM TwelveNumbers;
Where the expression DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
is used to get the first day of the current month without any time part (found here https://stackoverflow.com/a/1520803/880990).
An advanced way looks like this:
WITH AllNumbers AS
(
SELECT 1 AS Number
UNION ALL
SELECT Number+1
FROM AllNumbers
WHERE Number<12
)
INSERT INTO mytable (columnname)
SELECT DATEADD(month, -Number, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
FROM AllNumbers
UPDATE
As Martin points out, you want to insert all these dates into a single row. I would use a parametrized query for this, where the department and the current date (first month) are used as parameter
INSERT INTO mytable (Department, Month1, Month2, Month3, ..., Month12)
VALUES (
@dep,
DATEADD(month, -12, @dat),
DATEADD(month, -11, @dat),
DATEADD(month, -10, @dat),
...,
DATEADD(month, -2, @dat),
DATEADD(month, -1, @dat)
)
Or use the expression DATEADD(month, -N, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
with N = -12 ... -1 instead of a parameter. Note that GETDATE()
alone return the current day and includes the current time.
dateadd(month
? Also what is this table actually representing anyway?INSERT INTO
statement? msdn.microsoft.com/en-us/library/ms174335.aspx