Minggu, 26 Agustus 2012

sql month name

declare @start int,@end int
declare @qry nvarchar(100)
declare @summary nvarchar(1200)
set @start=1
set @end=13
set @summary=''
while not (@start=@end)
    begin
        if @start=@end-1
            set @qry=(select ('SELECT ('+convert(nvarchar,@start)+')monthNumber,            DateName(mm,DATEADD(mm,'+convert(nvarchar,@start)+',-1))monthName')qry)
        else
            set @qry=(select ('SELECT ('+convert(nvarchar,@start)+')monthNumber,            DateName(mm,DATEADD(mm,'+convert(nvarchar,@start)+',-1))monthName 
            union ')qry)

        set @summary=@summary+@qry       
        set @start=@start+1
    end
    exec (@summary)

#Results:
monthNumber    monthName
1              January
2              February
3              March
4              April
5              May
6              June
7              July
8              August
9              September
10             October
11             November
12             December

select DateName(mm,'2012-08-27 10:10:17.327')currMonthName

#Results:
currMonthName
August

2 komentar:

  1. You don't need the complexity of either a While Loop or Dynamic SQL do to this.

    The following snippet does the same thing without the loop or the dynamic SQL.

    WITH
    cteTally AS
    (
    SELECT TOP 12
    N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM sys.all_columns
    )
    SELECT MonthNumber = N,
    MonthName = DATENAME(mm,DATEADD(mm,N,-1))
    FROM cteTally
    ;

    Of course, if you've got a Tally Table, the code becomes even simpler.

    SELECT MonthNumber = N,
    MonthName = DATENAME(mm,DATEADD(mm,N,-1))
    FROM dbo.Tally
    WHERE N <= 12
    ;

    For more information on what a Tally Table (or cteTally) is and how it can be used to replace certain loops in a very high performance fashion, please see the following article.
    http://www.sqlservercentral.com/articles/T-SQL/62867/

    BalasHapus
  2. Jeff ... thank a lot for your explanation.

    BalasHapus