Want to display 12 months name from sql server

af7jpaap 于 2023-03-28 发布在 SQL Server

关注(0)|答案(9)|浏览(233)

I want to display 12 months name from sql server. i though to accomplish insert month name into temp table and then fire select statement on that table. so i had to write 12 insert table to insert 12 months name. so i search google to find better solution and i got it.

here is the sql statement

WITH R(N) AS
(
    SELECT 0
    UNION ALL
    SELECT N+1 
    FROM R
    WHERE N < 12
)

SELECT LEFT(DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())),3) AS [month]
FROM R

the above script works perfectly but my problem is i just do not understand how it works. i never work with CTE.

so tell me what is the meaning of WITH R(N) AS

and see this sql

SELECT LEFT(DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())),3) AS [month] FROM R
when above sql execute how it is getting value for -N ??
because here i have not set anything for -N ??

so please anyone help me to understand how whole thing works. thaks

My Second Phase of Question

just have look a and tell me

;WITH months(MonthNumber) AS
(
    SELECT 0
    UNION ALL
    SELECT MonthNumber+1 
    FROM months
    WHERE MonthNumber < 12
)

onething is not clear to me that why only first time the below part execute

SELECT 0
UNION ALL
SELECT MonthNumber+1 
FROM months
WHERE MonthNumber < 12

and from the 2nd time only this below part execute

SELECT MonthNumber+1 
FROM months
WHERE MonthNumber < 12

whenever we write two sql statement using Union and execute then always it return data from two sql state but specially in this case from the 2nd time why only this below part execute

SELECT MonthNumber+1 
FROM months
WHERE MonthNumber < 12

basically i not familiar with CTE with recursion technique and that is why things is not getting clear to me. if possible please discuss how CTE recursion works.

DECLARE @TotaDays SMALLINT
DECLARE @Month VARCHAR(15)
DECLARE @Year SMALLINT
DECLARE @date DATETIME 
SET @Month = 'January'
SET @Year = 2015
SET @date = '01 ' + @Month + ' ' + CONVERT(VARCHAR(4),@Year)
SET @TotaDays = 0
SELECT @TotaDays = DATEDIFF(DAY, @date, DATEADD(MONTH, 1, @date))

;WITH months(MonthNumber) AS
(
    SELECT 1
    UNION ALL
    SELECT MonthNumber+1 
    FROM months
    WHERE MonthNumber < @TotaDays
)

select * from months;

sql-server

来源：https://stackoverflow.com/questions/14155268/want-to-display-12-months-name-from-sql-server

9条答案

按热度按时间

dffbzjpn1#

The With R(N) is a Common Table Expression . From MDSN:
A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

The R is the name of the result set (or table) that you are generating. And the N is the month number.

This CTE in particular is a Recursive Common Table Expression . From MSDN:
A common table expression (CTE) provides the significant advantage of being able to reference itself, thereby creating a recursive CTE. A recursive CTE is one in which an initial CTE is repeatedly executed to return subsets of data until the complete result set is obtained.

When using CTE my suggestion would to be more descriptive with the names. So for your example you could use the following:

;WITH months(MonthNumber) AS
(
    SELECT 0
    UNION ALL
    SELECT MonthNumber+1 
    FROM months
    WHERE MonthNumber < 12
)
select *
from months;

In my version the months is the name of the result set that you are producing and the monthnumber is the value. This produces a list of the Month Numbers from 0-12 (See Demo).

Result:

| MONTHNUMBER |
---------------
|           0 |
|           1 |
|           2 |
|           3 |
|           4 |
|           5 |
|           6 |
|           7 |
|           8 |
|           9 |
|          10 |
|          11 |
|          12 |

Then the SELECT statement immediately after is using the values of the CTE result set to get you the Month Names.

Final query (See Demo):

;WITH months(MonthNumber) AS
(
    SELECT 0
    UNION ALL
    SELECT MonthNumber+1 
    FROM months
    WHERE MonthNumber < 12
)
SELECT LEFT(DATENAME(MONTH,DATEADD(MONTH,-MonthNumber,GETDATE())),3) AS [month]
FROM months;

赞(0）回复(0）举报 2023-03-28

vd2z7a6w2#

This query is to obtain all month names and month no's

SELECT DATENAME(MONTH, DATEADD(MM, s.number, CONVERT(DATETIME, 0))) AS [MonthName], 
MONTH(DATEADD(MM, s.number, CONVERT(DATETIME, 0))) AS [MonthNumber] 
FROM master.dbo.spt_values s 
WHERE [type] = 'P' AND s.number BETWEEN 0 AND 11
ORDER BY 2

赞(0）回复(0）举报 2023-03-28

j2datikz3#

Try This,

with Months as 
( 
    select month(GETDATE()) as Monthnumber, datename(month, GETDATE()) as name, 1 as number
    union all
    select month(dateadd(month,number,(GETDATE()))) Monthnumber ,datename(month, dateadd(month,number,(GETDATE()))) as name, number+1 
    from Months 
    where number<12
)   
select Monthnumber, name 
from Months 
order by Monthnumber

赞(0）回复(0）举报 2023-03-28

j2cgzkjk4#

R defines the name of the CTE, and (N) defines the name of the column(s) in the CTE - in this case there is only one column.

You can see that you are selecting from R in the second part of the statement.

You are indeed setting the value of (N) when you select 0 , i.e. the first and only column in the anchor portion of the CTE definition and later on when specifying select N+1 in the recursive part of the CTE definition.

赞(0）回复(0）举报 2023-03-28

thtygnil5#

Try changing the second part of the code, that way you will see what is generated in the recursive part:

WITH R(N) AS
(
    SELECT 0
    UNION ALL
    SELECT N+1 
    FROM R
    WHERE N < 12
)

SELECT *
FROM R

You will obtain this result:

By the way, i think it would be better to do SELECT 1 instead of SELECT 0 , so that you generate exactly 12 numbers and therefore you won't end up having an extra month

赞(0）回复(0）举报 2023-03-28

wmtdaxz36#

This query obtain information per period of time and get the list:

set language  'SPANISH'
DECLARE @table table(fechaDesde datetime , fechaHasta datetime ) 
INSERT @table VALUES('20151231' , '20161231');
WITH x AS 
    (
        SELECT   DATEADD( m , 1 ,fechaDesde ) as fecha  FROM @table
        UNION ALL
        SELECT  DATEADD( m , 1 ,fecha )
        FROM @table t INNER JOIN x ON  DATEADD( m , 1 ,x.fecha ) <= t.fechaHasta
    )
SELECT LEFT( CONVERT( VARCHAR, fecha , 112 ) , 6 ) as Periodo_Id 
,DATEPART ( dd, DATEADD(dd,-(DAY(fecha)-1),fecha)) Num_Dia_Inicio
,DATEADD(dd,-(DAY(fecha)-1),fecha) Fecha_Inicio
,DATEPART ( mm , fecha ) Mes_Id
,DATEPART ( yy , fecha ) Anio
,DATEPART ( dd, DATEADD(dd,-(DAY(DATEADD(mm,1,fecha))),DATEADD(mm,1,fecha))) Num_Dia_Fin
,DATEADD(dd,-(DAY(DATEADD(mm,1,fecha))),DATEADD(mm,1,fecha)) ultimoDia
,datename(MONTH, fecha) mes
,'Q' + convert(varchar(10),  DATEPART(QUARTER, fecha)) Trimestre_Name
FROM x 
OPTION(MAXRECURSION 0)

赞(0）回复(0）举报 2023-03-28

72qzrwbm7#

I modified to code shared earlier to give 12-month names with their respective month numbers in required sequence. Also, removed the dependency on the current date by adding 1st Jan 1900 as the fixed date.

WITH R (N)
AS
(SELECT
        0
    UNION ALL
    SELECT
        N + 1
    FROM R
    WHERE N < 12)

SELECT
    N
   ,DATENAME(MONTH, DATEADD(MONTH, N - 1, '1 Jan 1900')) AS [month]
FROM R
WHERE N > 0

赞(0）回复(0）举报 2023-03-28

u1ehiz5o8#

SELECT 
    DATE_FORMAT(date_range, '%Y-%M') AS Months
FROM 
    (SELECT DATE_ADD('2025-01-01', INTERVAL seq MONTH) AS date_range 
     FROM   (SELECT 0 AS seq UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL 
             SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 
             UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL 
             SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 
             UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL 
             SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31) v
     WHERE  seq < 366
    ) AS date_ranges where DATE_FORMAT(date_range, '%Y') =2025;

this gives the answer as

Months
2025-January
2025-February
2025-March
2025-April
2025-May
2025-June
2025-July
2025-August
2025-September
2025-October
2025-November
2025-December

赞(0）回复(0）举报 2023-03-28

vyu0f0g19#

I think you are expecting this by seeing this you can understand Thank you.

with months(mnum) as (select 0 union all select mnum+1 from months where mnum <11) select left(datename(month,dateadd(month,mnum,getdate())),3) as mn from months

David raja

赞(0）回复(0）举报 2023-03-28

我来回答

Want to display 12 months name from sql server

here is the sql statement

My Second Phase of Question

9条答案

相关问题

热门标签

最新问答