SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Display on Month and Year from date formula ??


Display on Month and Year from date formula ??

Author
Message
ScottPletcher
ScottPletcher
SSCrazy Eights
SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)

Group: General Forum Members
Points: 8044 Visits: 7163
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. Blush 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)[size=2]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.[/size]
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88048 Visits: 41128
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. Blush 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ScottPletcher
ScottPletcher
SSCrazy Eights
SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)

Group: General Forum Members
Points: 8044 Visits: 7163
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. Blush 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 BlushBlush

So sorry, I misread it.

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]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.[/size]
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26440 Visits: 17557
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 Modens 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
dwain.c
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7431 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 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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
below86
below86
SSC Eights!
SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)

Group: General Forum Members
Points: 943 Visits: 2446
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, '')

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88048 Visits: 41128
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88048 Visits: 41128
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7431 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
dwain.c
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7431 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search