CODEWITHSUNDEEP
sqlsql-serversql-server-2008date
DonOfDen
DonOfDen

Reputation: 4098

SQL 2008 R2 GROUP BY YEAR / MONTH Error

I am working with SQL R2 2008. I am trying to select date based on YEAR, Months in Year. I am trying to Group By YEAR in nvarchar.

My record in SQL has the Date format stored as like this (dd/mm/yyyy hh:mm)

10/11/2015 10:01

Record In DB Sample

With the help of previous question in Stack - How to Group by Year I tried the following:

SELECT
  T.[Date]
FROM (SELECT
  CONVERT(varchar, CAST([U_DATE_TIME_VALUE] AS datetime), 3) AS [Date],
  ROW_NUMBER() OVER (PARTITION BY YEAR(CAST([U_DATE_TIME_VALUE] AS datetime)) ORDER BY (SELECT
    1)
  ) AS rn
FROM [FuelData]) AS T
WHERE T.rn = 1
ORDER BY T.[Date]

But I get the following Year.

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

  1. How can I overcome this issue.?
  2. What if I want to select Year and Month alone from the Record. (i.e. 11/2015 alone from the record).

Can some one pls help me. Thank you in advance.

Upvotes: 2

Views: 193

Answers (2)

robotik
robotik

Reputation: 2007

I'm not sure you have found the best post as an example for what you're trying to achieve.

  1. What if I want to select Year and Month alone from the Record. (i.e. 11/2015 alone from the record).

If it is indeed in

dd/mm/yyyy hh:mm

format, you can use SUBSTRING to select part of this string.

SELECT SUBSTRING(U_DATE_TIME_VALUE,4,7) FROM [FuelData] should do the trick.

SELECT SUBSTRING(U_DATE_TIME_VALUE,7,4) FROM [FuelData] if you only want the year.

As for your initial question try something like this for annual grouping

SELECT 
  COUNT(*) AS [Units per year], 
  SUBSTRING(U_DATE_TIME_VALUE,7,4) AS [Year], 
  SUM(CAST(U_UNIT_AMT AS FLOAT)) AS [Amt per year]
FROM [FuelData]
GROUP BY SUBSTRING(U_DATE_TIME_VALUE,7,4)
ORDER BY SUBSTRING(U_DATE_TIME_VALUE,7,4)

Or if you want it grouped and ordered by month/year then

SELECT 
  COUNT(*) AS [Units per month], 
  SUBSTRING(U_DATE_TIME_VALUE,4,7) AS [Month], 
  SUM(CAST(U_UNIT_AMT AS FLOAT)) AS [Amt per month]
FROM [FuelData]
GROUP BY SUBSTRING(U_DATE_TIME_VALUE,4,7)
ORDER BY SUBSTRING(SUBSTRING(U_DATE_TIME_VALUE,4,7),4,4), SUBSTRING(U_DATE_TIME_VALUE,4,7)

Upvotes: 1

Henk van Boeijen
Henk van Boeijen

Reputation: 7928

This should do the trick:

select convert(datetime, '10/11/2014 13:44', 103)

So, your code would look like this:

SELECT
  T.[Date]
FROM (SELECT
  CONVERT(datetime, [U_DATE_TIME_VALUE], 103) AS [Date],
  ROW_NUMBER() OVER (PARTITION BY YEAR(CONVERT(datetime, [U_DATE_TIME_VALUE], 103))
        ORDER BY (SELECT 1)
    ) AS rn
    FROM [FuelData]
) AS T
WHERE T.rn = 1
ORDER BY T.[Date]

Upvotes: 0

Related Questions