select datepart year then group by and having datepart

sql datepart(month and year)
sql date from datetime
sql select where year equals
datepart sql
sql datepart(month name)
datepart access
sql dateadd
how to get quarter from month in sql

I did this and working properly but now I want how what to do that show month and year like 01 2018,

ALTER PROCEDURE [dbo].[SaleChart]  
    (@FromYear NVARCHAR(50),
     @ToYear NVARCHAR(50))
AS
BEGIN
    SELECT 
        SUM ([TotalQty]) AS 'Total',
        DATEPART (yyyy, [Date]) AS 'Year'
    FROM
        [dbo].[SalesMainTable]
    GROUP BY 
        DATEPART (yyyy, [Date])
    HAVING 
        DATEPART (yyyy, [Date]) >= @FromYear 
        AND DATEPART (yyyy, [Date]) <= @ToYear
END

Is this what you are looking for?

Note having it only required when filtering on aggregate values. Just use where otherwise.

ALTER PROCEDURE [dbo].[SaleChart]
(
  @FromYear NVARCHAR(50)
  , @ToYear NVARCHAR(50)
)
AS
BEGIN
  SELECT 
    DATEPART(year, [Date]) AS 'Year'
    , DATEPART(month, [Date]) AS 'Month'
    , SUM ([TotalQty]) AS 'Total'
  FROM [dbo].[SalesMainTable]
  WHERE DATEPART(year, [Date]) >= @FromYear AND DATEPART(year, [Date]) <= @ToYear
  GROUP BY DATEPART(year, [Date]), DATEPART(month, [Date])
END

Edit: If you are looking to filter by month as well I would pass that in as a date as follows:

ALTER PROCEDURE [dbo].[SaleChart]
(
  @FromDate date
  , @ToDate date
)
AS
BEGIN
  SELECT 
    DATEPART(year, [Date]) AS 'Year'
    , DATEPART(month, [Date]) AS 'Month'
    , SUM ([TotalQty]) AS 'Total'
  FROM [dbo].[SalesMainTable]
  WHERE [Date] >= @FromDate and [Date] <= @ToDate
  GROUP BY DATEPART(year, [Date]), DATEPART(month, [Date])
END

SQL Server DATEPART() Function By Practical Examples, In this tutorial, you will learn how to use the SQL Server DATEPART() DATEPART() function in the SELECT , WHERE , HAVING , GROUP BY  SQL Server DATEPART () function overview. The DATEPART () function returns an integer which is a part of a date such as a day, month, and year. The following shows the syntax of the DATEPART () function: DATEPART ( date_part , input_date ) The DATEPART () takes two arguments: date_part is the part of a date to be extracted.


add select and group by Month and Day

SELECT 
       SUM ([TotalQty]) AS 'Total',
       DATEPART (year,[Date]) AS 'Year',
       DATEPART (month,[Date]) AS 'Month',
       DATEPART (day,[Date]) AS 'Day'
FROM [dbo].[SalesMainTable]

where  DATEPART (year,[Date]) between @FromYear and @ToYear

GROUP BY DATEPART (year,[Date])
       ,DATEPART (month,[Date])
       ,DATEPART (day,[Date])

SQL Server YEAR() Function By Practical Examples, In this tutorial, you will learn how to use the SQL Server YEAR() function The YEAR() function returns the same value as the following DATEPART() function: If the input date value has only time data, the YEAR() function will return 1900 : IS NOT NULL GROUP BY YEAR(shipped_date) order by [year];. The DATEPART() function returns an integer value whose unit is denoted by the datepart argument. Examples. The following example extracts the year from a date: SELECT DATEPART (year, '2018-07-21 15:30:20.05') year; The output is: year -----2018 The following example illustrates how to extract the quarter from a date:


This is the right one

ALTER PROCEDURE [dbo].[SaleChartnew] ( @FromYear NVARCHAR(50), @ToYear NVARCHAR(50) ) AS BEGIN

DECLARE 
    @YearFrom   INT = RIGHT(@FromYear, 4)
,   @YearTo     INT = RIGHT(@ToYear, 4)
,   @MonthFrom  INT = LEFT(@FromYear, CHARINDEX('/',@FromYear) - 1)
,   @MonthTo    INT = LEFT(@ToYear, CHARINDEX('/',@ToYear) - 1)
,   @StartDate  DATE
,   @EndDate    DATE 


SET @StartDate = DATEFROMPARTS(@YearFrom, @MonthFrom, 1)
SET @EndDate   = DATEFROMPARTS(@YearTo, @MonthTo + 1, 1)
SET @EndDate   = DATEADD(DAY, -1, @EndDate)


SELECT 
    SUM([TotalQty]) AS 'Total'
 ,  YEAR([Date])    AS 'Year'
 ,  MONTH([Date])   AS 'Month'
FROM 
    [dbo].[SalesMainTable]

    where
 [Date] >= @StartDate
AND [Date] <= @EndDate

GROUP BY 
   Datepart (YEAR,[Date]),DATEPART (MONTH,[Date])
,   MONTH([Date])   

END

DATEPART SQL function, This article explores the DATEPART SQL function and its use in writing t-SQL queries. @OrderDate AS OrderDate;. SELECT DATEPART(Quarter, @​OrderDate) AS Quarter_OrderDate; Similarly, January 1st, 2012 is having the first week in ISO 8601 and week 53rd in US week. THEN 'Monday'. SELECT COUNT (*) as TotalOrders, DATEADD (month, DATEDIFF (month, 0, OrderDate),0) as OrderDate. FROM Orders. GROUP BY DATEADD (month, DATEDIFF (month, 0, OrderDate),0) ORDER BY OrderDate. OUTPUT. The above query generates 23 rows and rounds off to the first day in each month.


I think you're looking for something like this :

ALTER PROCEDURE [dbo].[SaleChart]
( @FromYear NVARCHAR(50), @ToYear NVARCHAR(50) ) 
AS BEGIN

    DECLARE 
        @YearFrom   INT = RIGHT(@FromYear, 4)
    ,   @YearTo     INT = RIGHT(@ToYear, 4)
    ,   @MonthFrom  INT = LEFT(@FromYear, CHARINDEX('/',@FromYear) - 1)
    ,   @MonthTo    INT = LEFT(@ToYear, CHARINDEX('/',@ToYear) - 1)
    ,   @StartDate  DATE
    ,   @EndDate    DATE 


    SET @StartDate = DATEFROMPARTS(@YearFrom, @MonthFrom, 1)
    SET @EndDate   = DATEFROMPARTS(@YearTo, @MonthTo + 1, 1)
    SET @EndDate   = DATEADD(DAY, -1, @EndDate)


    SELECT 
        SUM([TotalQty]) AS 'Total'
     ,  YEAR([Date])    AS 'Year'
     ,  MONTH([Date])   AS 'Month'
    FROM 
        [dbo].[SalesMainTable]
    GROUP BY 
        YEAR([Date])
    ,   MONTH([Date])   
    HAVING 
        [Date] >= @StartDate
    AND [Date] <= @EndDate

END

The current date format input used is mm/yyyy for both parameters. if you are going to change the date format in the future, you'll need to adjust the LEFT and RIGHT for each of them and maybe use SUBSTRING instead. Not sure why you used this way of date input as it'll make things complicated. I would suggest you to use one of the standard date formats that SQL Server understands such as yyyy-mm-dd as inputs, and in your query, you can use YEAR,MONTH, DAY or DATEPART functions to group the results as you need.

SQL Server DATEPART() Function, SQL Server DATEPART() Function. ❮ SQL Server Functions. Example. Return a specified part of a date: SELECT DATEPART(year,  DATEPART can be used in the select list, WHERE, HAVING, GROUP BY, and ORDER BY clauses. DATEPART implicitly casts string literals as a datetime2 type in SQL Server 2019 (15.x). This means that DATENAME does not support the format YDM when the date is passed as a string.


GROUP BY (Transact-SQL), A SELECT statement clause that divides the query result into groups This also applies to expressions in the HAVING clause. xml data type methods. The following examples fail because the GROUP BY clause has more than 4096 groups. the total sales for each year by using the DATEPART function. SQL HOME SQL Intro SQL Syntax SQL Select SQL Select Distinct SQL Where SQL And, Or, Not SQL Order By SQL Insert Into SQL Null Values SQL Update SQL Delete SQL Select Top SQL Min and Max SQL Count, Avg, Sum SQL Like SQL Wildcards SQL In SQL Between SQL Aliases SQL Joins SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Self Join SQL


Using DATEADD, DATEDIFF and DATEPART T-SQL Functions in , DATEPART function is used to return a part of a given date in a numeric value. Getting Year of the Order Date SELECT DATEPART(YEAR the date then Month needs to be passed into the DATEPART function as follows: SELECT COUNT(*) FROM my_table WHERE CAST(my_datetime AS DATE) = '2015-07-28' now, with the DATEPARTs. This takes 10+ minutes. SELECT COUNT(*) FROM my_table WHERE DATEPART(yy,CAST(my_datetime AS DATE)) = 2015 AND DATEPART(wk,CAST(my_datetime AS DATE)) = 31 AND DATEPART(dw,CAST(my_datetime AS DATE)) = 3


The Guru's Guide to Transact-SQL, The query is already grouping on the yr column; all it has to do to summarize the annual boundary: SELECT DATEPART(yy, OrderDate) AS 'Year', COUNT(​CASE DATEPART(qq, OrderDate) WHEN 1 THEN 1 ELSE NULL END) AS FROM Orders GROUP BY DATEPART(yy, OrderDate) ORDER BY 1 Year Ql Q2 Q3 Q4  A typical use of DATEPART() with week is to group data by week via the GROUP BY clause. We also use it in the SELECT clause to display the week number. Have a look at the query below and its result: