Anyone know why when i run this query the Yr Column in coming back 203111 instead of 2011 SELECT name, count (DISTINCT Convert(varchar(10), finishdate, 120)) as fdate , year(finshdate) * 100 + datepart(YY, finshdate) as YR FROM t1 Group BY name, year(finishdate) * 100 + datepart(YY,finishdate)
When I run your query and substitute (finishdate) with getdate() I get correct results. Since you are pulling from a table T1 for a distinct value from column finishdate, have you validated that you do not in fact have an erroneous year value of 2031 in your data set?
If we rewrite the query: declare @finishdate datetime = getdate() SELECT YEAR(@finishdate), year(@finishdate) * 100, DATEPART(YY,@finishdate), year(@finishdate) * 100 + datepart(YY, @finishdate) This returns: 2011 201100 2011 203111 What you're doing is multiplying the year by 100 and then adding it to itself. Or, indeed, multiplying it by 101. Whichever. This is almost certainly not what you were aiming to achieve. If you just want the year, then use either `YEAR(finishdate)` ***or*** `Datepart(YY,finishdate)` - there's no need to use both.
Multiplying a year by 100 to then add the month number on it is not the way to work with date. SQL Server can be a bit awkward when things get complex but in general the date functions let you achieve what you want with pretty simple code. @ThomasRushton has given you some good examples of getting the value you want. For me, if you want a year and month combination that is YYYYMM then you wont be doing anything with it that involves calculations (If you were - such as adding some months onto a YYYYMM combination then let us know as this is not the best way to go.) then I would change the Year and Month value datatypes to CHAR and then the + becomes a concatenating function rather than a summing function. So... DECLARE @tbl TABLE ( MyDate DATE ) INSERT INTO @tbl ( [MyDate] ) VALUES ( '2011-08-10' -- MyDate - date ), ( '2010-03-11' -- MyDate - date ), ( '2001-08-21' -- MyDate - date ) SELECT CONVERT(CHAR(4), YEAR([t].[MyDate])) + CONVERT(CHAR(2), REPLICATE('0', 2 - LEN(MONTH([t].[MyDate]))) + CONVERT(CHAR(2), MONTH([t].[MyDate]))) AS [Date_as_YYYYMM] FROM @tbl AS t