2

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.

Community
  • 1
  • 1
DonOfDen
  • 3,968
  • 11
  • 62
  • 112
  • 1
    post some data to understand more. – Ajay2707 Jan 12 '16 at 07:32
  • @Ajay2707 I have added the DB record pls check it. – DonOfDen Jan 12 '16 at 07:42
  • 1
    can you add output too, to understand your error. sorry for late comment – Ajay2707 Jan 12 '16 at 07:43
  • The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value. - @Ajay2707 – DonOfDen Jan 12 '16 at 07:44
  • That is the output I am getting @Ajay2707 – DonOfDen Jan 12 '16 at 07:45
  • I cannot see from your data what data format your string is in (day/month or month/day ??), but obviously it is in a format that cannot be converted without additional information. So if you use SQL Server try using CONVERT and provide the correct format type. Check out: https://msdn.microsoft.com/en-us/library/ms187928.aspx – Allan S. Hansen Jan 12 '16 at 07:52
  • I created a sql script and run. I does not get any error, try this and tell me where i am wrong. Declare @t table (code varchar(100) , U_date_time_value datetime, U_Unit_Amt numeric(18,2)) --this is dummy data.. Insert into @t values('218033..', '10/11/2015 10:01',290.01 ), ('218033..', '10/11/2015 10:01',231.01 ) – Ajay2707 Jan 12 '16 at 07:53
  • Date format interpretation when using non-ISO is dependant on server settings, so some might get errors, others might not. That's why it's better to either stay in ISO format or you have to supply the format code for the convert. – Allan S. Hansen Jan 12 '16 at 07:58
  • Why the MySQL tag? (Don't tag products not involved...) – jarlh Jan 12 '16 at 08:02
  • @jarlh It suggesion from StackOverflow.. I hope u will write about this in Stack development website.. – DonOfDen Jan 12 '16 at 08:07
  • The format of date is dd/mm/yyyy hh:mm – DonOfDen Jan 12 '16 at 08:10
  • @Ajay2707 can you change date in ur insert and try Because ur script is working for me bu when i run for my table alone it shows error. – DonOfDen Jan 12 '16 at 08:16
  • pl. then give your table structure too – Ajay2707 Jan 12 '16 at 08:22
  • @TomPHP I have used a nVarchar column to store date/time in a database and run your query and it has run fine without any error. Please check the data to confirm that all the rows in the column U_Date_Time_Value correspond to the correct datetime format (Even though the data type is nVarchar) – Kumar C Jan 12 '16 at 08:29
  • There is not direct functon to extract the MM/YYYY, you can do tha using dateparts and joining them. Ex:`select convert(varchar,datepart(mm,getdate()))+ '/' + convert(varchar,datepart(year,getdate()))` returns **1/2016** – Kumar C Jan 12 '16 at 08:31
  • The error is easy to solve. the problem is your U_DATA_TIME_VALUE date type is not datetime or date, it's nvarchar. So you need to turn it into datetime first. check this out : http://stackoverflow.com/questions/19218982/convert-nvarchar-to-datetime-in-sql-server-2008 – Raffaello.D.Huke Jan 12 '16 at 09:45
  • I cant change it to dataetime thats why i need some other solution @Raffaello.D.Huke – DonOfDen Jan 12 '16 at 10:51
  • You can go through this link: http://stackoverflow.com/questions/20838344/sql-the-conversion-of-a-varchar-data-type-to-a-datetime-data-type-resulted-in Hope this will help. – Richa Kumari Jan 12 '16 at 13:58
  • I don't have access to a SQL Server but the style 3 for `CONVERT` is for year without century try instead 103 – Serpiton Jan 12 '16 at 14:51

2 Answers2

1

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)
robotik
  • 1,837
  • 1
  • 20
  • 26
0

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]
Henk van Boeijen
  • 7,357
  • 6
  • 32
  • 42