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! Thanks in advance!
-
I understand that. Forgot that little nugget
What I need it to do is where YYYYMM >= current month -4 where the current month is a variable but I don't know how to do that! That then creates another problem when the year changes...Thanks. -
Assuming YYYYMM column is a string, try this:
where
cast(YYYYMM+'01' as datetime) >=
DATEADD(mm,-4, getdate())
messy, but it should work. (though I'm sure one of the real gurus will come up with a fancier solution...)
Any chance of changing the DDL so that YYYYMM is a real date? That would be a much better option as you could use date calculations and various datetime functions to display the YYYYMM format if needed.
-
What datatype is YYYYMM and what format/datatype is 'current month'
Assuming, like Pam, that YYYYMM is char and 'current month' is the same format/type as YYYYMM then
DECLARE @start char(6)
SET @start = CONVERT(char(6),DATEADD(month,-4,CAST([currentmonth]+'01' as datetime)),112)
SELECT ...
WHERE YYYYMM >= @start
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply