Custom Report error: number of installations by month and year

[Anonymous]

Hello,

I had this report working without problems with a custom mantained table, where I had "installed date" of a computer as a DATE field, now I wanted to erase the custom mantained table and use standard Lansweeper tables but I couldn't convert the InstallDate field from text to table, any ideias?

SELECT     YEAR(InstallDate) AS Year, COUNT(*) AS Installations
FROM         dbo.tblOperatingsystem
GROUP BY MONTH(InstallDate), YEAR(InstallDate)

Thanks,
Paulo

[Anonymous]

Well, I had problems with the "+" character that exist in the field "InstallDate". I figured it out doing some trims:

SELECT     TOP (100) PERCENT YEAR(LEFT(InstallDate, 8)) AS Year, MONTH(LEFT(InstallDate, 8)) AS Month, COUNT(*) AS Installations
FROM         dbo.tblOperatingsystem
GROUP BY MONTH(LEFT(InstallDate, 8)), YEAR(LEFT(InstallDate, 8))
ORDER BY Year, Month