UGH! This is what happens when people try to be "smart" about how to store dates and times except for maybe in a Calendar table. This problem would be a cake walk if the data were stored correctly for processing rather than some poor attempt to save 2 or 4 bytes of space or saving the display value instead of the real value.
--===== If this were in a table, you could add a constraint to accept only first-of-month dates.
DECLARE @BetterYrMo DATE = '20150101' --OR DATETIME OR SMALLDATETIME OR even DATETIME2
SELECT CONVERT(CHAR(6),DATEADD(mm,1,@BetterYrMo),112) --CONVERT only for display purposes here.
--If they suddenly want a DASH between year and month, it's easy if the base data is temporal
--instead of an INT.
SELECT CONVERT(CHAR(7),DATEADD(mm,1,@BetterYrMo),121) --CONVERT only for display purposes here.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)