Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Display on Month and Year from date formula ?? Expand / Collapse
Author
Message
Posted Monday, February 18, 2013 4:32 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:47 PM
Points: 2,330, Visits: 3,510
Jeff Moden (2/18/2013)
ScottPletcher (2/18/2013)
declare @enddate datetime
set @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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1421373
Posted Tuesday, February 19, 2013 9:20 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 35,772, Visits: 32,443
ScottPletcher (2/18/2013)
Jeff Moden (2/18/2013)
ScottPletcher (2/18/2013)
declare @enddate datetime
set @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 Moden
"RBAR 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1421716
Posted Tuesday, February 19, 2013 10:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:47 PM
Points: 2,330, Visits: 3,510
Jeff Moden (2/19/2013)
ScottPletcher (2/18/2013)
Jeff Moden (2/18/2013)
ScottPletcher (2/18/2013)
declare @enddate datetime
set @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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1421750
Posted Wednesday, February 20, 2013 8:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:05 PM
Points: 13,327, Visits: 12,820
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)
Post #1422109
Posted Wednesday, February 20, 2013 5:52 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:50 PM
Points: 3,440, Visits: 5,397
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 DATETIME

SET @date = CURRENT_TIMESTAMP

SELECT @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_Added
2013-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!
Post #1422368
Posted Thursday, February 21, 2013 8:07 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, December 18, 2014 7:20 AM
Points: 313, Visits: 1,151
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 datetime
set @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, '')
Post #1422614
Posted Thursday, February 21, 2013 8:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 35,772, Visits: 32,443
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 DATETIME

SET @date = CURRENT_TIMESTAMP

SELECT @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_Added
2013-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 Moden
"RBAR 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1422853
Posted Thursday, February 21, 2013 10:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 35,772, Visits: 32,443
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 Moden
"RBAR 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1422881
Posted Thursday, February 21, 2013 11:01 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:50 PM
Points: 3,440, Visits: 5,397
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!
Post #1422883
Posted Thursday, February 21, 2013 11:21 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:50 PM
Points: 3,440, Visits: 5,397
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!
Post #1422887
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse