February 25, 2013 at 3:11 pm
Hi,
i have a view with two columns, lets say SpecYear and SpecMonth, both are integer.
How can I build two new columns with the first and last day of this year/month:pinch:
ex.
2013 02 => 2013-02-01 2013-02-28
etc.
thanx
--
candide
________Panta rhei
February 25, 2013 at 3:21 pm
candide (2/25/2013)
Hi,i have a view with two columns, lets say SpecYear and SpecMonth, both are integer.
How can I build two new columns with the first and last day of this year/month:pinch:
ex.
2013 02 => 2013-02-01 2013-02-28
etc.
thanx
This will work.
declare @Month int = 2, @Year int = 2013
declare @ThisDate datetime
set @ThisDate = cast(@Year as char(4)) + right('0' + cast(@Month as varchar(2)), 2) + '01'
select @ThisDate
select dateadd(mm, datediff(mm, 0, @ThisDate), 0) -- Beginning of this month
select dateadd(day, -1, dateadd(mm, datediff(mm, 0, @ThisDate) + 1, 0)) -- End of this month
Take a look at Lynn's article for a number of datetime routines here. http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url]
If at all possible you should consider storing datetime information as datetime instead of multiple integer columns.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 25, 2013 at 3:45 pm
This will work:
DECLARE @ThisYear INT,
@ThisMonth INT;
SET @ThisYear = 2013;
SET @ThisMonth = 2;
SELECT
DATEADD(MONTH, @ThisMonth - 1, DATEADD(YEAR, @ThisYear - 1900, CAST('19000101' AS DATETIME))) BOM,
DATEADD(DAY, -1, DATEADD(MONTH, @ThisMonth, DATEADD(YEAR, @ThisYear - 1900, CAST('19000101' AS DATETIME)))) EOM
February 25, 2013 at 10:38 pm
You can also cheat for performance with a little integer math. The 22800 is the year (1900*12). The "0" in the BOM forumula is 1900-01-01. The "-1" in the EOM formula is the day before that. Because of the integer math, it's very fast. I can't remember if it was Michael Valentine Jones or Peter Larsson that I first saw with this forumula.
DECLARE @ThisYear INT,
@ThisMonth INT;
SELECT @ThisYear = 2013,
@ThisMonth = 2;
SELECT BOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth-1, 0),
EOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth ,-1)
Of course, the "-1" in the BOM formula can be distributed to the other constant to make the formula a bit shorter, still.
DECLARE @ThisYear INT,
@ThisMonth INT;
SELECT @ThisYear = 2012, --Leap Year!
@ThisMonth = 2;
SELECT BOM = DATEADD(mm, @ThisYear*12-22801+@ThisMonth, 0),
EOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth,-1)
Both will also work correctly for dates before 1900 without modification and Leap Years are also figured correctly.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 26, 2013 at 4:43 am
Hi,
Jeff's solution works great:-P
exactly what I needed
thanx
--
candide
________Panta rhei
February 26, 2013 at 5:09 am
And another alternative solution, based on 'strings'
declare @Month int = 2, @Year int = 2013
declare @ThisDate datetime = convert(varchar(8),@year*10000+@month*100+01)
select CONVERT(varchar(7),@thisdate,121)+'-01'
select CONVERT(varchar(7),dateadd(mm,1,@thisdate),121)+'-01'
ben brugman
February 26, 2013 at 7:59 am
ben.brugman (2/26/2013)
And another alternative solution, based on 'strings'
declare @Month int = 2, @Year int = 2013declare @ThisDate datetime = convert(varchar(8),@year*10000+@month*100+01)
select CONVERT(varchar(7),@thisdate,121)+'-01'
select CONVERT(varchar(7),dateadd(mm,1,@thisdate),121)+'-01'
ben brugman
Just be aware that string conversions of dates will be a bit slower than integer conversions. It'll take a million rows to notice a difference but every bit helps when you're working with large tables or millions of hits each day.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 26, 2013 at 8:01 am
candide (2/26/2013)
Hi,Jeff's solution works great:-P
exactly what I needed
thanx
Thank you for the kind feedback. I just want to make sure because you're the one that will have to support it. Do you understand how and why it works?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 26, 2013 at 3:43 pm
The integer value for the first day of the month is even easier:
SELECT BOM = @ThisYear * 10000 + @ThisMonth * 100 + 1,
but you must CAST it to char(8) before storing it in a date/datetime column.
DECLARE @BOM datetime
SELECT @BOM = CAST(@ThisYear * 10000 + @ThisMonth * 100 + 1 AS char(8))
SELECT @BOM
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 26, 2013 at 5:29 pm
Jeff Moden (2/25/2013)
You can also cheat for performance with a little integer math. The 22800 is the year (1900*12). The "0" in the BOM forumula is 1900-01-01. The "-1" in the EOM formula is the day before that. Because of the integer math, it's very fast. I can't remember if it was Michael Valentine Jones or Peter Larsson that I first saw with this forumula.
DECLARE @ThisYear INT,@ThisMonth INT;
SELECT @ThisYear = 2013,
@ThisMonth = 2;
SELECT BOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth-1, 0),
EOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth ,-1)
Of course, the "-1" in the BOM formula can be distributed to the other constant to make the formula a bit shorter, still.
DECLARE @ThisYear INT,@ThisMonth INT;
SELECT @ThisYear = 2012, --Leap Year!
@ThisMonth = 2;
SELECT BOM = DATEADD(mm, @ThisYear*12-22801+@ThisMonth, 0),
EOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth,-1)
Both will also work correctly for dates before 1900 without modification and Leap Years are also figured correctly.
That formula was a bit of a joint effort that Peter Larsson and I developed on this thread:
Make Date function (like in VB)
February 26, 2013 at 6:20 pm
Michael Valentine Jones (2/26/2013)
Jeff Moden (2/25/2013)
You can also cheat for performance with a little integer math. The 22800 is the year (1900*12). The "0" in the BOM forumula is 1900-01-01. The "-1" in the EOM formula is the day before that. Because of the integer math, it's very fast. I can't remember if it was Michael Valentine Jones or Peter Larsson that I first saw with this forumula.
DECLARE @ThisYear INT,@ThisMonth INT;
SELECT @ThisYear = 2013,
@ThisMonth = 2;
SELECT BOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth-1, 0),
EOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth ,-1)
Of course, the "-1" in the BOM formula can be distributed to the other constant to make the formula a bit shorter, still.
DECLARE @ThisYear INT,@ThisMonth INT;
SELECT @ThisYear = 2012, --Leap Year!
@ThisMonth = 2;
SELECT BOM = DATEADD(mm, @ThisYear*12-22801+@ThisMonth, 0),
EOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth,-1)
Both will also work correctly for dates before 1900 without modification and Leap Years are also figured correctly.
That formula was a bit of a joint effort that Peter Larsson and I developed on this thread:
Make Date function (like in VB)
Thanks, Michael. This time, I'm going to add that URL to the code snippet.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 26, 2013 at 8:13 pm
Just to emphasize the performance differences between using a string conversion or not for date conversions, let's do a little test... a race, really.
Here's the test data. Since all of the code under test is so very fast on today's machines, I made 10 Million rows of test data. If you do, in fact, have a fairly recent machine, the table will only take about 12 seconds to be built and populated.
--===== Conditionally drop the test table to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL
DROP TABLE #TestTable
;
--===== Create and populate the test table on-the-fly.
SELECT TOP 10000000
TheYear = ABS(CHECKSUM(NEWID()))%14+2000,
TheMonth = ABS(CHECKSUM(NEWID()))%12+1
INTO #TestTable
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
GO
Here are the actual tests. For those not familiar with the "@BitBucket" style of testing, the variable captures the result of the calculation and dumps it to take the display time and unnecessary disk processing time out of the picture so we're measuring just the result of the formulas/methods.
RAISERROR('========== INTEGER MATH Conversion ==============================',0,1) WITH NOWAIT;
DECLARE @Bitbucket DATETIME;
SET STATISTICS TIME ON;
SELECT @Bitbucket = DATEADD(mm, TheYear*12-22801+TheMonth, 0)
FROM #TestTable;
SET STATISTICS TIME OFF;
GO
RAISERROR('========== Lynn''s Conversion ==============================',0,1) WITH NOWAIT;
DECLARE @Bitbucket DATETIME;
SET STATISTICS TIME ON;
SELECT @Bitbucket = DATEADD(MONTH, TheMonth - 1, DATEADD(YEAR, TheYear - 1900, CAST('19000101' AS DATETIME)))
FROM #TestTable;
SET STATISTICS TIME OFF;
GO
RAISERROR('========== Scott''s INT Conversion ==============================',0,1) WITH NOWAIT;
DECLARE @Bitbucket int;
SET STATISTICS TIME ON;
SELECT @Bitbucket = TheYear * 10000 + TheMonth * 100 + 1
FROM #TestTable;
SET STATISTICS TIME OFF;
GO
RAISERROR('========== Scott''s IMPLICIT STRING Conversion ==============================',0,1) WITH NOWAIT;
DECLARE @Bitbucket DATETIME;
SET STATISTICS TIME ON;
SELECT @Bitbucket = CAST(TheYear * 10000 + TheMonth * 100 + 1 AS CHAR(8))
FROM #TestTable;
SET STATISTICS TIME OFF;
GO
Here are the results from my laptop (I5 4 core 64 bit processor running at 2.5 GHz with 6GB RAM).
========== INTEGER MATH Conversion ==============================
SQL Server Execution Times:
CPU time = 3151 ms, elapsed time = 3146 ms.
========== Lynn's Conversion ==============================
SQL Server Execution Times:
CPU time = 3229 ms, elapsed time = 3241 ms.
========== Scott's INT Conversion ==============================
SQL Server Execution Times:
CPU time = 2262 ms, elapsed time = 2253 ms.
========== Scott's IMPLICIT STRING Conversion ==============================
SQL Server Execution Times:
CPU time = 6318 ms, elapsed time = 6313 ms.
A couple of points to bring up here.
First, I only used the code examples that were easy to convert to a full table test. It is enough, I believe, to drive the main point home.
As long as Lynn's good code looks, he wisely stuck to integer math and the intrinsic date/time functions. Because of that, his code is nearly as fast as the Integer Math method.
If you look at Scott's INT conversion method, it absolutely smokes everything else. Still, we end up with an integer date, which may not be what you need, but it works VERY well for doing things like outputing ISO dates to a file.
Now, the main point. To be sure, this has nothing to do with Scott's abilities and I'm not picking on him. He did, after all, write some of the most compact code there is for the tassk. He just happens to have written the best code to make the point. The only difference between Scott's INT conversion and his conversion to datetime is the explicit conversion to a string and the implict conversion to DATETIME. Both require a string conversion. As you can see, the string conversion code is more that twice as slow as the Integer Math conversion and nearly 3 times slower than the pure INT conversion. Yes, I have to agree with you that a lousy 3 second difference across 10 Million rows doesn't seem like much but, consider this... how much would you have to spend on hardware to double the speed of your code?
If you double the speed of all of your code just by using simple tricks like avoiding string conversions in DATETIME calculations, just imagine how fast your apps would actually run. You could finally unplug the garden-hose from your CPU coolers. 😛
Like Granny used to say... "Mind the pennies and the dollars will take care of themselves." 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
February 28, 2013 at 3:26 am
Jeff Moden (2/26/2013)
candide (2/26/2013)
Hi,Thank you for the kind feedback. I just want to make sure because you're the one that will have to support it. Do you understand how and why it works?
Jeff,
I searched some time for a solution, but most answers work with string handling like 'CAST blabla varchar blabla' which may work but is not what I think of adjacent handling of date values. A numeric solution like yours I never saw before, and it's simple and fast. Nothing more to say...
Waiting for cool date values handling in next SQL Server version...:-)
--
candide
________Panta rhei
February 28, 2013 at 10:27 am
Jeff Moden (2/26/2013)
Just to emphasize the performance differences between using a string conversion or not for date conversions, let's do a little test... a race, really.Here's the test data. Since all of the code under test is so very fast on today's machines, I made 10 Million rows of test data. If you do, in fact, have a fairly recent machine, the table will only take about 12 seconds to be built and populated.
--===== Conditionally drop the test table to make reruns in SSMS easier.IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL
DROP TABLE #TestTable
;
--===== Create and populate the test table on-the-fly.
SELECT TOP 10000000
TheYear = ABS(CHECKSUM(NEWID()))%14+2000,
TheMonth = ABS(CHECKSUM(NEWID()))%12+1
INTO #TestTable
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
GO
Here are the actual tests. For those not familiar with the "@BitBucket" style of testing, the variable captures the result of the calculation and dumps it to take the display time and unnecessary disk processing time out of the picture so we're measuring just the result of the formulas/methods.
RAISERROR('========== INTEGER MATH Conversion ==============================',0,1) WITH NOWAIT;DECLARE @Bitbucket DATETIME;
SET STATISTICS TIME ON;
SELECT @Bitbucket = DATEADD(mm, TheYear*12-22801+TheMonth, 0)
FROM #TestTable;
SET STATISTICS TIME OFF;
GO
RAISERROR('========== Lynn''s Conversion ==============================',0,1) WITH NOWAIT;
DECLARE @Bitbucket DATETIME;
SET STATISTICS TIME ON;
SELECT @Bitbucket = DATEADD(MONTH, TheMonth - 1, DATEADD(YEAR, TheYear - 1900, CAST('19000101' AS DATETIME)))
FROM #TestTable;
SET STATISTICS TIME OFF;
GO
RAISERROR('========== Scott''s INT Conversion ==============================',0,1) WITH NOWAIT;
DECLARE @Bitbucket int;
SET STATISTICS TIME ON;
SELECT @Bitbucket = TheYear * 10000 + TheMonth * 100 + 1
FROM #TestTable;
SET STATISTICS TIME OFF;
GO
RAISERROR('========== Scott''s IMPLICIT STRING Conversion ==============================',0,1) WITH NOWAIT;
DECLARE @Bitbucket DATETIME;
SET STATISTICS TIME ON;
SELECT @Bitbucket = CAST(TheYear * 10000 + TheMonth * 100 + 1 AS CHAR(8))
FROM #TestTable;
SET STATISTICS TIME OFF;
GO
Here are the results from my laptop (I5 4 core 64 bit processor running at 2.5 GHz with 6GB RAM).
========== INTEGER MATH Conversion ==============================SQL Server Execution Times:
CPU time = 3151 ms, elapsed time = 3146 ms.
========== Lynn's Conversion ==============================
SQL Server Execution Times:
CPU time = 3229 ms, elapsed time = 3241 ms.
========== Scott's INT Conversion ==============================
SQL Server Execution Times:
CPU time = 2262 ms, elapsed time = 2253 ms.
========== Scott's IMPLICIT STRING Conversion ==============================
SQL Server Execution Times:
CPU time = 6318 ms, elapsed time = 6313 ms.
A couple of points to bring up here.
First, I only used the code examples that were easy to convert to a full table test. It is enough, I believe, to drive the main point home.
As long as Lynn's good code looks, he wisely stuck to integer math and the intrinsic date/time functions. Because of that, his code is nearly as fast as the Integer Math method.
If you look at Scott's INT conversion method, it absolutely smokes everything else. Still, we end up with an integer date, which may not be what you need, but it works VERY well for doing things like outputing ISO dates to a file.
Now, the main point. To be sure, this has nothing to do with Scott's abilities and I'm not picking on him. He did, after all, write some of the most compact code there is for the tassk. He just happens to have written the best code to make the point. The only difference between Scott's INT conversion and his conversion to datetime is the explicit conversion to a string and the implict conversion to DATETIME. Both require a string conversion. As you can see, the string conversion code is more that twice as slow as the Integer Math conversion and nearly 3 times slower than the pure INT conversion. Yes, I have to agree with you that a lousy 3 second difference across 10 Million rows doesn't seem like much but, consider this... how much would you have to spend on hardware to double the speed of your code?
If you double the speed of all of your code just by using simple tricks like avoiding string conversions in DATETIME calculations, just imagine how fast your apps would actually run. You could finally unplug the garden-hose from your CPU coolers. 😛
Like Granny used to say... "Mind the pennies and the dollars will take care of themselves." 😀
Perhaps. But I think my code is instantly understandable when read. So unless you know or expect to be processing huge number of rows, developer time is multiple orders of magnitude more expensive than computer clock-time seconds.
Btw, weren't you the one that did testing that demonstrated that SET STATISTICS TIME ON itself caused, for example, scalar functions to appear worse than they really are? I'm not saying it's known, but isn't it possible that the process itself of determining the time used caused some of the spike in time? Although I know all string manipulation/handling in SQL Server is relatively extremely slow.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 28, 2013 at 10:52 am
This cut down number of CPU cycles when performing multiplication as it takes less number of bit-moves for multiplying smaller numbers - I should accept that it's hard to measure with existing technology :hehe: (plus it doesn't use some strange number of 22800):
selectdateadd(month, (@YearParam - 1900)*12 + @MonthParam- 1, 0) AS BOM
selectdateadd(month, (@YearParam - 1900)*12 + @MonthParam, -1) AS EOM
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply