SQL Server Performance Forum – Threads Archive

T-sQL datetime question

I have the following script: SELECT TOP 100 PERCENT ‘RVUPTConsecutive’ AS RVUPT, r1.Campus, r1.YYYYMM, SUM(r1.RVUs) / SUM(r1.Visits) AS RVUPerVisit,
SUBSTRING(r1.YYYYMM, 5, 2) + ‘/1/’ + SUBSTRING(r1.YYYYMM, 1, 4) AS Month
FROM PCS_DERD.[Rollup – Productivity] r1 INNER JOIN
PCS_DERD.[Rollup – Productivity] r2 ON r1.Campus = r2.Campus
GROUP BY r1.Campus, r1.YYYYMM
ORDER BY r1.YYYYMM DESC But I only want results for the last 4 months….I’m missing something small here but I can’t for the life of me figure it out! I know I need a where clause but I need it to be a variable that is declared in a stored procedure that is looking at this view. Thanks in advance!

---

Hi ya, this is a bit trickier due to the fact that your ‘date’ is stored as a 6 byte string. It would have been preferred to save them as an actual date. Then indexing could help performance… where convert( datetime, r1.YYYYMM + ’01’ ) between @param and dateadd( month, -4, @param ) Cheers
Twan

---

http://www.sql-server-performance.com/fk_datetime.asp Madhivanan Failing to plan is Planning to fail

---

]]>