query to return month strings for year

15 Sep 2006 2:21 PM

NH

Hi,

Is there an sql query I can use to return the following data..

january 2006
february 2006
march 2006
etc
december 2006

Until now I have just been writing sql to add these values to a table
variable one by one. Is there a query that will return all the 12 values at
once and can then be used to do a single Insert statement

e.g.
Insert @tableResults
select * from bla bla

15 Sep 2006 2:43 PM

Aaron Bertrand [SQL Server MVP]

If you have a numbers table you can say

SELCT TOP 12 DATENAME(MONTH, DATEADD(MONTH, Number, '19991201')) + ' 2006'
    FROM Numbers n
    WHERE Number > 0
    ORDER BY Number;

See the following article regarding a numbers table:
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

Show quote

"NH" <N*@discussions.microsoft.com> wrote in message
news:CDA8BF4E-6679-46FB-A3A1-D91367D65B07@microsoft.com...
> Hi,
>
> Is there an sql query I can use to return the following data..
>
> january 2006
> february 2006
> march 2006
> etc
> december 2006
>
> Until now I have just been writing sql to add these values to a table
> variable one by one. Is there a query that will return all the 12 values
> at
> once and can then be used to do a single Insert statement
>
> e.g.
> Insert @tableResults
> select * from bla bla

15 Sep 2006 3:02 PM

Vern Rabe

Or, an ANSI compatible and maybe better performing version:

SELECT DATENAME(MONTH, DATEADD(MONTH, n.Number, '19991201')) + ' 2006'
FROM Numbers n
WHERE n.Number BETWEEN 1 AND 12;

Vern Rabe

Show quote

"Aaron Bertrand [SQL Server MVP]" wrote:

> If you have a numbers table you can say
>
> SELCT TOP 12 DATENAME(MONTH, DATEADD(MONTH, Number, '19991201')) + ' 2006'
>     FROM Numbers n
>     WHERE Number > 0
>     ORDER BY Number;
>
> See the following article regarding a numbers table:
> http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html
>
>
>
>
> "NH" <N*@discussions.microsoft.com> wrote in message
> news:CDA8BF4E-6679-46FB-A3A1-D91367D65B07@microsoft.com...
> > Hi,
> >
> > Is there an sql query I can use to return the following data..
> >
> > january 2006
> > february 2006
> > march 2006
> > etc
> > december 2006
> >
> > Until now I have just been writing sql to add these values to a table
> > variable one by one. Is there a query that will return all the 12 values
> > at
> > once and can then be used to do a single Insert statement
> >
> > e.g.
> > Insert @tableResults
> > select * from bla bla
>
>
>

15 Sep 2006 3:22 PM

Aaron Bertrand [SQL Server MVP]

Sure. But ORDER BY is still important, I'm guessing.

Show quote

"Vern Rabe" <VernR***@discussions.microsoft.com> wrote in message
news:C4550EDF-B5FB-41A2-92E3-EAE03DE2A02F@microsoft.com...
> Or, an ANSI compatible and maybe better performing version:
>
> SELECT DATENAME(MONTH, DATEADD(MONTH, n.Number, '19991201')) + ' 2006'
> FROM Numbers n
> WHERE n.Number BETWEEN 1 AND 12;
>
> Vern Rabe

15 Sep 2006 2:46 PM

Paul Ibison

I did this as a bit of fun:

declare @mydate datetime, @count tinyint
set @mydate = '2006-01-01'

set @count = 1

while @count <= 12

begin

select datename(month,@mydate), year(@mydate) -- your insert goes here

set @mydate = dateadd(mm,1,@mydate)

set @count = @count + 1

end

However, in practice I'd probably use excel to create the inserts!

Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com