August 10, 2018 at 7:42 pm
This gives me last month, can't figure out next month.
DECLARE @yrMo int = 201501
select CONVERT(varchar(6),CONVERT(datetime, CONCAT(@yrMo,'01'), 112)-1,112)
August 10, 2018 at 7:45 pm
Geez, I'm stoopid, LOL ..this works but is there a better way
DECLARE @yrMo int = 201501
select CONVERT(varchar(6),CONVERT(datetime, CONCAT(@yrMo,'01'), 112)+31,112)
August 10, 2018 at 11:13 pm
texpic - Friday, August 10, 2018 7:45 PMGeez, I'm stoopid, LOL ..this works but is there a better way
DECLARE @yrMo int = 201501
select CONVERT(varchar(6),CONVERT(datetime, CONCAT(@yrMo,'01'), 112)+31,112)
This will break 5 times for every year, in the months that have less than 31 days!
😎
Here is a better method, which does the calculation correctly and avoids the string conversion.
DECLARE @YRMO INT = 201501;
DECLARE @MSHIFT INT = 1;
SELECT DATEADD(MONTH,((@YRMO % 100) -1) + @MSHIFT,DATEADD(YEAR,((@YRMO / 100) - 1900),0)) AS THE_DATE
August 11, 2018 at 4:24 pm
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.
;
--Jeff Moden
Change is inevitable... Change for the better is not.
August 12, 2018 at 8:55 am
Jeff Moden - Saturday, August 11, 2018 4:24 PMUGH! 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.
;
We call them "data type spastic" and "numerically inward focused", just like waiting for a coherent response from management, don't hold the breath because it's an oxymoron!
😎
Sorting and grouping by numerical date values may have some benefits but any type of calculations and manipulations will suffer big time!
August 12, 2018 at 3:36 pm
Eirikur Eiriksson - Sunday, August 12, 2018 8:55 AMJeff Moden - Saturday, August 11, 2018 4:24 PMUGH! 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.
;We call them "data type spastic" and "numerically inward focused", just like waiting for a coherent response from management, don't hold the breath because it's an oxymoron!
😎Sorting and grouping by numerical date values may have some benefits but any type of calculations and manipulations will suffer big time!
You already know this so just saying it out loud for anyone else that reads this that may be unaware...
GROUPing is a calculation. If someone decides that they also want to see SUMs (for example) by QUARTER and YEAR, having dates stored in the YYYYMM format is going to cause the big time suffering that you speak of.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 13, 2018 at 7:21 am
Eirikur Eiriksson - Friday, August 10, 2018 11:13 PMtexpic - Friday, August 10, 2018 7:45 PMGeez, I'm stoopid, LOL ..this works but is there a better way
DECLARE @yrMo int = 201501
select CONVERT(varchar(6),CONVERT(datetime, CONCAT(@yrMo,'01'), 112)+31,112)
This will break 5 times for every year, in the months that have less than 31 days!
😎Here is a better method, which does the calculation correctly and avoids the string conversion.
DECLARE @YRMO INT = 201501;
DECLARE @MSHIFT INT = 1;SELECT DATEADD(MONTH,((@YRMO % 100) -1) + @MSHIFT,DATEADD(YEAR,((@YRMO / 100) - 1900),0)) AS THE_DATE
It actually won't break because it doesn't matter if the day is the first or the third or some other day. In the end, the day is truncated.
I'm not sure about speed, but this might be simple for anyone to figure out what the code is doing and still use always the first day of the month.
DECLARE @yrMo int = 201501;
SELECT CONVERT(char(6),DATEADD( MM, 1, CONCAT( @yrMo,'01')), 112);
August 14, 2018 at 9:27 am
Luis Cazares - Monday, August 13, 2018 7:21 AMEirikur Eiriksson - Friday, August 10, 2018 11:13 PMtexpic - Friday, August 10, 2018 7:45 PMGeez, I'm stoopid, LOL ..this works but is there a better way
DECLARE @yrMo int = 201501
select CONVERT(varchar(6),CONVERT(datetime, CONCAT(@yrMo,'01'), 112)+31,112)
This will break 5 times for every year, in the months that have less than 31 days!
😎Here is a better method, which does the calculation correctly and avoids the string conversion.
DECLARE @YRMO INT = 201501;
DECLARE @MSHIFT INT = 1;SELECT DATEADD(MONTH,((@YRMO % 100) -1) + @MSHIFT,DATEADD(YEAR,((@YRMO / 100) - 1900),0)) AS THE_DATE
It actually won't break because it doesn't matter if the day is the first or the third or some other day. In the end, the day is truncated.
I'm not sure about speed, but this might be simple for anyone to figure out what the code is doing and still use always the first day of the month.
DECLARE @yrMo int = 201501;SELECT CONVERT(char(6),DATEADD( MM, 1, CONCAT( @yrMo,'01')), 112);
Luis, what Eirikur is saying breaks is adding 31 days, especially if one happens to start on January 28th. Using actual date math with DATEADD solves that problem, and that's all that Eirikur did. Getting into the habit of using character-based date manipulation may work if you are anally consistent in being sure your calculation is correct, but one slip and kaboom! Much safer to stick entirely with tested date math and DATEADD.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 14, 2018 at 9:47 am
sgmunson - Tuesday, August 14, 2018 9:27 AMLuis, what Eirikur is saying breaks is adding 31 days, especially if one happens to start on January 28th. Using actual date math with DATEADD solves that problem, and that's all that Eirikur did. Getting into the habit of using character-based date manipulation may work if you are anally consistent in being sure your calculation is correct, but one slip and kaboom! Much safer to stick entirely with tested date math and DATEADD.
But it will never start on January 28th, as the OP is using 01 as the day. I do agree that tested date math and DATEADD/DATEDIFF are much better options.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply