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


Formatting Dates with 3 Character Months (SQL Spackle)


Formatting Dates with 3 Character Months (SQL Spackle)

Author
Message
william-700725
william-700725
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 190
Michael Valentine Jones (1/27/2011)
william-700725 (1/27/2011)
About time somebody pointed out the convenience of the standardized formats. I've been using UCASE( CONVERT( char ( 11 ) , GETDATE() , 106 ) ) to create mil-spec dates for longer than I care to remember.

Would it be excessively pedantic to note that format 120 (ODBC canonical [yyyy-mm-dd hh:mi:ss]) is another handy date string, especially when the output is going somewhere where some high-drag, low-speed user will want to sort the dates alphabetically (don't ask why)?



I prefer to use format 121 to get the full precision of the DATETIME and DATETIME2 datatypes in a sortable character string.

Example:


select
convert(varchar(23),getdate(),121) as [Datetime],
convert(varchar(27),sysdatetime(),121) as [Datetime2]



Results:


Datetime Datetime2
----------------------- ---------------------------
2011-01-27 12:58:07.567 2011-01-27 12:58:07.5678911




That's a good idea when the added precision is significant and accurate. On the other hand, when data is getting entered on a daily basis when things work right, the added precision is too easily confused with added accuracy.

In my own experience, CONVERT( char ( 16 ) , GETDATE() , 120 ) is usually more than precise enough without adding extra volume to the the output -- in fact, more often than not I'll trim it down to char( 10 ).

Lest you think I'm maligning my users' average attention to detail, I will state for the record that I'm reasonably sure that at least 3 out of any given 4 can breath with their mouth closed.

David McKinney
David McKinney
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2723 Visits: 2090
Jeff Moden (1/23/2011)
You bet. Thanks for the feedback, the tips, and a couple of other things.

As a side bar, recursive CTE's tend to be a bit slow and resource intensive for doing splits. You might want to consider one of the other methods.


Everything is relative!

My recursive CTE was EXTREMELY FAST and I was more than happy with it until I compared against the tally table alternative, when suddenly it became EXTREMELY SLOW!!


SET NOCOUNT ON
--Supress the auto-display of rowcounts for appearance/speed
DECLARE @StartTime datetime
--Timer to measure total duration
SET @StartTime = GETDATE()

--===== Split or "Normalize" the whole table at once
SELECT mh.lcid ,row_number() over ( partition by mh.lcid order by t.N),
SUBSTRING(',' + mh.months + ',', N + 1,
CHARINDEX(',', ',' + mh.months + ',', N + 1) - N - 1) AS Value
FROM dbo.Tally t
CROSS JOIN sys.syslanguages mh
WHERE N < LEN(',' + mh.months + ',')
AND SUBSTRING(',' + mh.months + ',', N, 1) = ','

--===== Display the total duration
SELECT STR(DATEDIFF(ms, @StartTime, GETDATE())) + ' Milliseconds duration'

SET @StartTime = GETDATE()
;With Months as
(select sll.lcid,sll.shortmonths,1 as MonthNumber, 1 as Position, charindex(',', sll.shortmonths,2) as endPosition FROM sys.syslanguages sll
UNION ALL
select sl.lcid, sl.shortmonths,m.MonthNumber+1, m.endPosition+1 as StartPosition, CHARINDEX(',',sl.shortmonths+',', m.endPosition+1) from sys.syslanguages sl
inner join Months m on m.lcid=sl.lcid
where CHARINDEX(',',sl.shortmonths, m.Position+1)<>0
) --SELECT lcid, m.shortmonths,m.Position, m.endPosition , m.endPosition-m.Position as sublength, len(m.shortmonths) as lenght FROM Months m

SELECT lcid, substring(m.shortmonths,m.Position, m.endPosition-m.Position+1) FROM Months m

SELECT STR(DATEDIFF(ms, @StartTime, GETDATE())) + ' Milliseconds duration'


william-700725
william-700725
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 190
David McKinney (1/28/2011)


Everything is relative!

My recursive CTE was EXTREMELY FAST and I was more than happy with it until I compared against the tally table alternative, when suddenly it became EXTREMELY SLOW!!


Been there, felt that, and still can't ditch the danged T-shirt...
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211219 Visits: 41977
David McKinney (1/28/2011)
Jeff Moden (1/23/2011)
You bet. Thanks for the feedback, the tips, and a couple of other things.

As a side bar, recursive CTE's tend to be a bit slow and resource intensive for doing splits. You might want to consider one of the other methods.


Everything is relative!

My recursive CTE was EXTREMELY FAST and I was more than happy with it until I compared against the tally table alternative, when suddenly it became EXTREMELY SLOW!!


SET NOCOUNT ON
--Supress the auto-display of rowcounts for appearance/speed
DECLARE @StartTime datetime
--Timer to measure total duration
SET @StartTime = GETDATE()

--===== Split or "Normalize" the whole table at once
SELECT mh.lcid ,row_number() over ( partition by mh.lcid order by t.N),
SUBSTRING(',' + mh.months + ',', N + 1,
CHARINDEX(',', ',' + mh.months + ',', N + 1) - N - 1) AS Value
FROM dbo.Tally t
CROSS JOIN sys.syslanguages mh
WHERE N < LEN(',' + mh.months + ',')
AND SUBSTRING(',' + mh.months + ',', N, 1) = ','

--===== Display the total duration
SELECT STR(DATEDIFF(ms, @StartTime, GETDATE())) + ' Milliseconds duration'

SET @StartTime = GETDATE()
;With Months as
(select sll.lcid,sll.shortmonths,1 as MonthNumber, 1 as Position, charindex(',', sll.shortmonths,2) as endPosition FROM sys.syslanguages sll
UNION ALL
select sl.lcid, sl.shortmonths,m.MonthNumber+1, m.endPosition+1 as StartPosition, CHARINDEX(',',sl.shortmonths+',', m.endPosition+1) from sys.syslanguages sl
inner join Months m on m.lcid=sl.lcid
where CHARINDEX(',',sl.shortmonths, m.Position+1)<>0
) --SELECT lcid, m.shortmonths,m.Position, m.endPosition , m.endPosition-m.Position as sublength, len(m.shortmonths) as lenght FROM Months m

SELECT lcid, substring(m.shortmonths,m.Position, m.endPosition-m.Position+1) FROM Months m

SELECT STR(DATEDIFF(ms, @StartTime, GETDATE())) + ' Milliseconds duration'



BWAA-HAA!!! Thanks for the great feedback, David. And, just to make sure, I have to tell you that that version of the Tally table splitter has become a "slow" version. I wrote a new one about two weeks ago that I'm going to add to the modernization of my original Tally Table article.

--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
UMG Developer
UMG Developer
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3990 Visits: 2204
Jeff Moden (2/2/2011)
BWAA-HAA!!! Thanks for the great feedback, David. And, just to make sure, I have to tell you that that version of the Tally table splitter has become a "slow" version. I wrote a new one about two weeks ago that I'm going to add to the modernization of my original Tally Table article.


I can't wait, when do you think it will be released?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211219 Visits: 41977
UMG Developer (2/3/2011)
Jeff Moden (2/2/2011)
BWAA-HAA!!! Thanks for the great feedback, David. And, just to make sure, I have to tell you that that version of the Tally table splitter has become a "slow" version. I wrote a new one about two weeks ago that I'm going to add to the modernization of my original Tally Table article.


I can't wait, when do you think it will be released?


Not sure on that. Still doing some performance and "what if" and "it depends" testing on it along with everything else. I have a couple of versions of the same thing that I'm comparing for ease-of-use and performance.

--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
peter-757102
peter-757102
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1725 Visits: 2559
Jeff Moden (1/21/2011)
Very cool, Peter! Thanks for the coding, the testing, and the hypothesis! I'll do some additional testing over the weekend.


Not sure if your testing caught my error, but in my latest test, I used "GetDate()" instead of "SomeDate", skewing my final results as a semi-constant is obviously easier on the processing when applied over many rows.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211219 Visits: 41977
I did miss that. Thank you for the update.

Looking back in this thread and switching gears a bit, the new improved splitter article did come out. An hour after it came out, someone came up with a 20% improvement even to the (significant) improvements that I made. I pretty much left the original article as it was because it showed how I came to making my improvemments. The extra 20% functions are located in the "Resources" link near the end of the article. Here's the link to the article.
http://www.sqlservercentral.com/articles/Tally+Table/72993/

--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
peter-757102
peter-757102
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1725 Visits: 2559
Jeff Moden (5/10/2012)
I did miss that. Thank you for the update.

Looking back in this thread and switching gears a bit, the new improved splitter article did come out. An hour after it came out, someone came up with a 20% improvement even to the (significant) improvements that I made. I pretty much left the original article as it was because it showed how I came to making my improvemments. The extra 20% functions are located in the "Resources" link near the end of the article. Here's the link to the article.
http://www.sqlservercentral.com/articles/Tally+Table/72993/


I know that particular splitter article as improvement was my own contribution after somone already brought a 10% boost.

And now we switched to the subject of the T-SQL string splitter, you will find great interst in this blog entry from Paul White:

http://sqlblog.com/blogs/paul_white/archive/2012/05/03/parallel-row-goals-gone-rogue.aspx

There is a bug of some sort in 2008 and 2008 SE (I am not sure about 2012) that causes runaway generation of rows by the CTE. The bigger the number of rows the CTE can deliver, the bigger the problem becomes. Lucky for us, the splitter was dedicated to just 8000 rows to support varchar fully and 20*20*20 using cross joins would suffice and limit the performance damage this worst case issue brings.

Due to this bug I don't hold my breath for an T-SQL based varchar(max) anymore as even occasional problems could cause the runtimes to spike unacceptably. Using .NET code for this particular situation seems the reliable way to go, without good T-SQL alternative to fall back on. Unless you found a breaktrough of some sort of course Smile

Kind regards,


Peter de Heer
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211219 Visits: 41977
peter-757102 (5/10/2012)
Jeff Moden (5/10/2012)
I did miss that. Thank you for the update.

Looking back in this thread and switching gears a bit, the new improved splitter article did come out. An hour after it came out, someone came up with a 20% improvement even to the (significant) improvements that I made. I pretty much left the original article as it was because it showed how I came to making my improvemments. The extra 20% functions are located in the "Resources" link near the end of the article. Here's the link to the article.
http://www.sqlservercentral.com/articles/Tally+Table/72993/


I know that particular splitter article as improvement was my own contribution after somone already brought a 10% boost.

And now we switched to the subject of the T-SQL string splitter, you will find great interst in this blog entry from Paul White:

http://sqlblog.com/blogs/paul_white/archive/2012/05/03/parallel-row-goals-gone-rogue.aspx

There is a bug of some sort in 2008 and 2008 SE (I am not sure about 2012) that causes runaway generation of rows by the CTE. The bigger the number of rows the CTE can deliver, the bigger the problem becomes. Lucky for us, the splitter was dedicated to just 8000 rows to support varchar fully and 20*20*20 using cross joins would suffice and limit the performance damage this worst case issue brings.

Due to this bug I don't hold my breath for an T-SQL based varchar(max) anymore as even occasional problems could cause the runtimes to spike unacceptably. Using .NET code for this particular situation seems the reliable way to go, without good T-SQL alternative to fall back on. Unless you found a breaktrough of some sort of course Smile

Kind regards,


Peter de Heer


I'll take a look at Paul's post but I believe the use of TOP overcomes that problem. It turns out that Ben-Gan added that particular enhancement just a little bit after he first published the cascading CTE method. I missed his article on that enhancement.

Thanks again for your improvement on the code in the aritcle, Peter.

--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
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