asked
Date Format
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?
No, I don;t have erroneous date. It works when I change the YY to M I would get a result 201101 for Jan 2011. But when I change it to YY it returns 203111
Ah, I spaced on that. My apologies. I was looking at the fdate portion of your query. So your year you are taking the year of finishdate multiplying by 100 and adding it to the year of finishdate. So 201100 plus 2011 = 203111. What exactly is the outcome you are wanting? A value of 2011, 201100, 201111, or 2011002011?
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
@Fatherjack - couldn't it be better expressed just using the CONVERT command and the date style option? SELECT CONVERT(varchar(6),MyDate,112) FROM @tbl The date style 112 is the ISO standard YYYYMMDD, converting the date to a varchar(6) will return the first 6 characters of that date style, job done.
