## Display on Month and Year from date formula ??

 Author Message ScottPletcher Hall of Fame Group: General Forum Members Points: 3946 Visits: 6684 Jeff Moden (2/18/2013)ScottPletcher (2/18/2013)`declare @enddate datetimeset @enddate = getdate()SELECT STUFF(CONVERT(varchar(10), DATEADD(DAY, -21, @enddate), 1), 3, 4, '')`Except for Sean, we did forget to answer the original question. Thanks, Scott.Huh?? I thought that was the original q: display 21 days before [@]enddate in format mm/yy. SQL DBA,SQL Server MVP('07, '08, '09)Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them." Jeff Moden SSC-Forever Group: General Forum Members Points: 45119 Visits: 39918 ScottPletcher (2/18/2013)Jeff Moden (2/18/2013)ScottPletcher (2/18/2013)`declare @enddate datetimeset @enddate = getdate()SELECT STUFF(CONVERT(varchar(10), DATEADD(DAY, -21, @enddate), 1), 3, 4, '')`Except for Sean, we did forget to answer the original question. Thanks, Scott.Huh?? I thought that was the original q: display 21 days before [@]enddate in format mm/yy.It was and my comment was meant to be a compliment to you. --Jeff ModenRBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.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.Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.Although change is inevitable, change for the better is not.Just because you can do something in PowerShell, doesnt mean you should. Helpful Links:How to post code problemsHow to post performance problemsForum FAQs ScottPletcher Hall of Fame Group: General Forum Members Points: 3946 Visits: 6684 Jeff Moden (2/19/2013)ScottPletcher (2/18/2013)Jeff Moden (2/18/2013)ScottPletcher (2/18/2013)`declare @enddate datetimeset @enddate = getdate()SELECT STUFF(CONVERT(varchar(10), DATEADD(DAY, -21, @enddate), 1), 3, 4, '')`Except for Sean, we did forget to answer the original question. Thanks, Scott.Huh?? I thought that was the original q: display 21 days before [@]enddate in format mm/yy.It was and my comment was meant to be a compliment to you.DOH So sorry, I misread it. SQL DBA,SQL Server MVP('07, '08, '09)Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them." Sean Lange SSCoach Group: General Forum Members Points: 16580 Visits: 17023 Jeff Moden (2/15/2013)Sean Lange (2/15/2013)You should use DATEADD because the simple math does not work with date or datetime2 datatypes.I have to ask... if you're not using the DATE or DATETIME2 datatypes, why does that matter?I would say it is the same type of reasoning behind using proper date formats. If you use dateadd it will continue to work even if somebody decides they need to change the datatype to datetime2 because they need more accuracy. Much like anything else we do, if there is a simple solution that makes your code more robust and you know about it why would you code the shortcut that might not function in the future? _______________________________________________________________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 Moden's splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2) dwain.c SSCarpal Tunnel Group: General Forum Members Points: 4255 Visits: 6431 Kingston Dhasian (2/18/2013)Jeff Moden (2/15/2013)I suppose consistency is a good reason. But, let's try something just for fun. Write some code to add 41:41:41.041 to a given date.My version`DECLARE @date DATETIMESET @date = CURRENT_TIMESTAMPSELECT @date AS Date, DATEADD(MILLISECOND, 41, DATEADD(SECOND, 41, DATEADD(MINUTE, 41, DATEADD(HOUR, 41, @date)))) AS Date_Added`I get the output as below`Date Date_Added2013-02-18 10:29:50.343 2013-02-20 04:11:31.383`The only strange thing I observed is that it always 40 milliseconds instead of 41 milliseconds.Is there any other strange thing?How about this?`DECLARE @TimeAdd VARCHAR(20) = '41:41:41.041'SELECT CAST(GETDATE() AS DATETIME2) ,DATEADD(millisecond, 1, DATEADD(hour, CAST(LEFT(@TimeAdd, 2) AS INT) ,DATEADD(millisecond ,DATEDIFF(millisecond, 0, CAST('00:'+RIGHT(@TimeAdd, 9) AS TIME)) ,CAST(GETDATE() AS DATETIME2))))`So Jeff, now that I've had my fun, how about showing us how you would do it? My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables below86 SSChasing Mays Group: General Forum Members Points: 634 Visits: 2121 Small correction to Scott's code, he has the STUFF for 4 characters, this will wipe out both slashes. You could use either one below to get 'MM/YY':declare @enddate datetimeset @enddate = getdate()SELECT STUFF(CONVERT(varchar(10), DATEADD(DAY, -21, @enddate), 1), 3, 3, '')SELECT STUFF(CONVERT(varchar(10), DATEADD(DAY, -21, @enddate), 1), 4, 3, '') -------------------------------------------------------------we travel not to escape life but for life not to escape us Jeff Moden SSC-Forever Group: General Forum Members Points: 45119 Visits: 39918 Jeff Moden (2/18/2013)Kingston Dhasian (2/18/2013)Jeff Moden (2/15/2013)I suppose consistency is a good reason. But, let's try something just for fun. Write some code to add 41:41:41.041 to a given date.My version`DECLARE @date DATETIMESET @date = CURRENT_TIMESTAMPSELECT @date AS Date, DATEADD(MILLISECOND, 41, DATEADD(SECOND, 41, DATEADD(MINUTE, 41, DATEADD(HOUR, 41, @date)))) AS Date_Added`I get the output as below`Date Date_Added2013-02-18 10:29:50.343 2013-02-20 04:11:31.383`The only strange thing I observed is that it always 40 milliseconds instead of 41 milliseconds.Is there any other strange thing?I'll try to get back to this after worrk tonight. In the mean time, remember that DATETIME has an accuracty of only 3.3 milliseconds. All DATETIMES will end with 0, 3, or 7 for the final digit in the milliseconds.My apologies, Kingston. My requirements weren't clear. The 41:41:41.041 was supposed to be a VARCHAR. --Jeff ModenRBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.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.Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.Although change is inevitable, change for the better is not.Just because you can do something in PowerShell, doesnt mean you should. Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Jeff Moden SSC-Forever Group: General Forum Members Points: 45119 Visits: 39918 dwain.c (2/20/2013)How about this?`DECLARE @TimeAdd VARCHAR(20) = '41:41:41.041'SELECT CAST(GETDATE() AS DATETIME2) ,DATEADD(millisecond, 1, DATEADD(hour, CAST(LEFT(@TimeAdd, 2) AS INT) ,DATEADD(millisecond ,DATEDIFF(millisecond, 0, CAST('00:'+RIGHT(@TimeAdd, 9) AS TIME)) ,CAST(GETDATE() AS DATETIME2))))`So Jeff, now that I've had my fun, how about showing us how you would do it?That works fine for DATETIME2 but my intent was to show how using simple date addition can make the code, well... simple. Not only does it make the code much more simple, but there's a performance advantage, as well, and I wish they had included date addition in the newer date related data types.Of course, without a typical million row test, claims of performance just hearsay. So, without further ado, here's my standard million row test table for such tests...`/********************************************************************************************************************** Purpose: Create a voluminous test table with various types of highly randomized data. --Jeff Moden **********************************************************************************************************************/--===== Conditionally drop the test table to make reruns easier IF OBJECT_ID('dbo.JBMTest','U') IS NOT NULL DROP TABLE dbo.JBMTest;--===== Create and populate a 1,000,000 row test table. -- "SomeID" has a range of 1 to 1,000,000 unique numbers -- "SomeInt" has a range of 1 to 50,000 non-unique numbers -- "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings -- "SomeMoney has a range of 0.00 to 100.00 non-unique numbers -- "SomeDateTime" has a range of >=01/01/2000 and <01/01/2020 non-unique date/times -- "SomeDate" has a range of >=01/01/2000 and <01/01/2020 non-unique "whole dates" -- "SomeName" contains random characters at random lengths from 2 to 20 characters SELECT TOP 1000000 SomeID = IDENTITY(INT,1,1), SomeInt = ABS(CHECKSUM(NEWID())) % 50000 + 1, SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID())) % (ASCII('Z')-ASCII('A')+1) +ASCII('A')) + CHAR(ABS(CHECKSUM(NEWID())) % (ASCII('Z')-ASCII('A')+1) +ASCII('A')), SomeMoney = CAST(RAND(CHECKSUM(NEWID())) * 100 AS DECIMAL(9,2)), --Note rounding SomeDateTime = RAND(CHECKSUM(NEWID())) * DATEDIFF(dd,'2000','2020') + CAST('2000' AS DATETIME), SomeDate = ABS (CHECKSUM(NEWID())) % DATEDIFF(dd,'2000','2020') + CAST('2000' AS DATETIME), SomeName = RIGHT(NEWID(),ABS(CHECKSUM(NEWID())) % 19 + 2) INTO dbo.JBMTest FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2; ALTER TABLE dbo.JBMTest ADD CONSTRAINT PK_JBMTest PRIMARY KEY CLUSTERED (SomeID) WITH FILLFACTOR = 90;`Here's the code being tested. Note that I did have to modify your code to work with DATETIME. The modifications were just to remove the CASTs to DATETIME2 that you did. Please check it to ensure that I faithfully kept the essence of the DATETIME2 compatible code.` RAISERROR('========== Simple date addition ==========',0,1) WITH NOWAIT;DECLARE @TimeToAdd CHAR(12), @BitBucket DATETIME; SELECT @TimeToAdd = '41:41:41.041'; SET STATISTICS TIME ON; SELECT @BitBucket = DATEADD(hh, ABS(LEFT(@TimeToAdd,2)), SomeDateTime+('00:'+RIGHT(@TimeToAdd,9))) FROM dbo.JBMTest; SET STATISTICS TIME OFF;GO RAISERROR('========== DATETIME2 Compatible ==========',0,1) WITH NOWAIT;DECLARE @TimeToAdd CHAR(12), @BitBucket DATETIME; SELECT @TimeToAdd = '41:41:41.041'; SET STATISTICS TIME ON; SELECT @BitBucket = DATEADD(ms, 1, DATEADD(hour, CAST(LEFT(@TimeToAdd, 2) AS INT) ,DATEADD(ms ,DATEDIFF(ms, 0, '00:'+RIGHT(@TimeToAdd, 9)) ,SomeDateTime))) FROM dbo.JBMTest; SET STATISTICS TIME OFF;`I'm sure that someone can write even faster code to demonstrate the performance advantage but here are the results I get on my older desktop box using SQL Server 2005 DE.`========== Simple date addition ==========SQL Server Execution Times: CPU time = 1156 ms, elapsed time = 1350 ms.========== DATETIME2 Compatible ==========SQL Server Execution Times: CPU time = 1625 ms, elapsed time = 1799 ms.`Of course, since we're adding the same amount of time to all the rows, it's also much more efficient to preconvert the string to a DATETIME and then use some even simpler date addition.` RAISERROR('========== Preconverted date addition ==========',0,1) WITH NOWAIT;DECLARE @TimeToAdd CHAR(12), @BitBucket DATETIME, @TimeToAddDT DATETIME; SELECT @TimeToAdd = '41:41:41.041'; SET STATISTICS TIME ON; SELECT @TimeToAddDT = DATEADD(hh, ABS(LEFT(@TimeToAdd,2)),('00:'+RIGHT(@TimeToAdd,9))) SELECT @BitBucket = SomeDateTime + @TimeToAddDT FROM dbo.JBMTest; SET STATISTICS TIME OFF;GO``========== Preconverted date addition ==========SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times: CPU time = 844 ms, elapsed time = 1007 ms.` --Jeff ModenRBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.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.Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.Although change is inevitable, change for the better is not.Just because you can do something in PowerShell, doesnt mean you should. Helpful Links:How to post code problemsHow to post performance problemsForum FAQs dwain.c SSCarpal Tunnel Group: General Forum Members Points: 4255 Visits: 6431 Jeff,When I try this:`SELECT GETDATE() ,DATEADD(hh, ABS(LEFT('41:41:41.041',2)), GETDATE()+('00:'+RIGHT('41:41:41.041',9)))`I get these results:`2013-02-22 12:54:58.843 2013-02-24 06:36:39.883`When I do the math 843+041=884 - so what's up with that 883 result? I tried it several times and it seemed to be consistently off by 1 ms.The only reason I went to DATETIME2 was thinking that the extra precision would take care of the 1 millisecond issue that arises when DATETIME is used with accuracy to the 3.3 ms you mentioned earlier. It turned out it didn't work for some unfathomable reason, hence my kludge of adding 1 ms to the result.I do like the simple elegance of your solution though (converting timetoadd to a DATETIME beforehand was particularly crafty), not to mention the high praise deserved for all the work building the test harness. If it weren't for that pesky 1 ms of inaccuracy, I'd give you a +1 (instead only a +0.95). :-) My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables dwain.c SSCarpal Tunnel Group: General Forum Members Points: 4255 Visits: 6431 Oh yes. And I also noticed that your conversion of my DATETIME2 code causes the same 1ms of inaccuracy. So presumably, if we're willing to live with 1ms of inaccuracy, we can change the DATETIME2 compatible version to this:` RAISERROR('========== DATETIME2 Compatible ==========',0,1) WITH NOWAIT;DECLARE @TimeToAdd CHAR(12), @BitBucket DATETIME; SELECT @TimeToAdd = '41:41:41.041'; SET STATISTICS TIME ON; SELECT @BitBucket = DATEADD(hour, CAST(LEFT(@TimeToAdd, 2) AS INT) ,DATEADD(ms ,DATEDIFF(ms, 0, '00:'+RIGHT(@TimeToAdd, 9)) ,SomeDateTime)) FROM dbo.JBMTest; SET STATISTICS TIME OFF;`Which results in a bit closer horse race:`========== Simple date addition ========== SQL Server Execution Times: CPU time = 266 ms, elapsed time = 271 ms.========== DATETIME2 Compatible ========== SQL Server Execution Times: CPU time = 280 ms, elapsed time = 291 ms.`Mine still loses mind you but not by as much and I never mind losing to a champion in the performance arena such as yourself! My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables