Want to Display 12 Months Name from SQL Server

Want to display 12 months name from sql server

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;

Get 12 month name with data count using SQL Server?

Using an adhoc calendar table to generate 12 months:

/* @StartDate = truncate `getdate()` to the start of the year: */
declare @StartDate datetime = dateadd(year , datediff(year , 0, getdate()), 0)

;with Months as (
select top (12) 
     m = row_number() over (order by number)
   ,[Month] = dateadd(month, row_number() over (order by number) -1, @StartDate)
  , NextMonth = dateadd(month, row_number() over (order by number), @StartDate)
  from master.dbo.spt_values
)
select 
    m.m
  , Month = format(m.Month, 'MMM-yy')
  , tally = count(c.InsertDateTime)
from Months m
  left join Comments c
    on c.InsertDateTime >= m.Month
   and c.InsertDateTime < m.NextMonth
group by m.m, format(m.Month, 'MMM-yy')
order by m

rextester demo: http://rextester.com/NNVI43016

returns:

+----+--------+-------+
| m  | Month  | tally |
+----+--------+-------+
|  1 | Jan-17 |     3 |
|  2 | Feb-17 |     0 |
|  3 | Mar-17 |     2 |
|  4 | Apr-17 |     0 |
|  5 | May-17 |     0 |
|  6 | Jun-17 |     0 |
|  7 | Jul-17 |     0 |
|  8 | Aug-17 |     0 |
|  9 | Sep-17 |     0 |
| 10 | Oct-17 |     0 |
| 11 | Nov-17 |     0 |
| 12 | Dec-17 |     1 |
+----+--------+-------+

This has the added advantage in that it is not calling functions on the columns in the larger table Comments, and it is using SARGable conditions for the join.

Reference:

• Bad habits to kick : mis-handling date / range queries - Aaron Bertrand
• SARGable functions in SQL Server - Rob Farley
• SARGable expressions and performance - Daniel Hutmachier
• Generate a set or sequence without loops - 1 - Aaron Bertrand
• Generate a set or sequence without loops - 2 - Aaron Bertrand
• Generate a set or sequence without loops - 3 - Aaron Bertrand
• The "Numbers" or "Tally" Table: What it is and how it replaces a loop - Jeff Moden

how to select last 12 months name and year without using tables using sql query?

SET LANGUAGE English;

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], 
       DATEPART(YEAR,DATEADD(MONTH,-N,GETDATE())) AS [year]
FROM R

Convert Month Number to Month Name Function in SQL

A little hacky but should work:

SELECT DATENAME(month, DATEADD(month, @mydate-1, CAST('2008-01-01' AS datetime)))

How to get previous 12 months data in SQL Server and avoiding the current month

The following should work, evaluates to >='2020-04-01 00:00:00.000' and <'2021-04-01 00:00:00.000' (which encompasses to end of March 23:59:59)

where Datecolumn >=DateAdd(month, DateDiff(month, 0, DateAdd(month,-12,GetDate())), 0)
and dateColumn < DateAdd(month, DateDiff(month, 0, GetDate()), 0)

How can I get the last 12 months from the current date PLUS extra days till 1st of the last month retrieved

Using DATEADD and DATEDIFF:

DECLARE @ThisDate DATE = '20150817'
SELECT DATEADD(YEAR, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @ThisDate), '19000101'))

For more common date routines, see this article by Lynn Pettis.

---

To use in your WHERE clause:

DECLARE @ThisDate DATE = '20150817'
SELECT *
FROM <your_table>
WHERE
    <date_column> >= DATEADD(YEAR, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @ThisDate), '19000101'))

Returning Month Name in SQL Server Query

This will give you the full name of the month.

select datename(month, S0.OrderDateTime)

If you only want the first three letters you can use this

select convert(char(3), S0.OrderDateTime, 0)

How to select the last 12 months in sql?

In Oracle and SQL-Server you can use ROW_NUMBER.

name = month name and num = month number:

  SELECT sub.name, sub.num
    FROM (SELECT ROW_NUMBER () OVER (PARTITION BY name ORDER BY num DESC) rn,
                 name,
                 num
            FROM tab) sub
   WHERE sub.rn = 1
ORDER BY num DESC;

Get month name from SQL Server date value in table column

You should try something like this

select convert(char(3), [date], 0)
select datename(month, [date])

sql server Get the FULL month name from a date

SELECT DATENAME(MONTH, GETDATE()) 
         + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY]

OR Date without Comma Between date and year, you can use the following

SELECT DATENAME(MONTH, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2))
           + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month DD YYYY]

---

Related Topics