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 «««7891011»»»

Formatting Dates with 3 Character Months (SQL Spackle) Expand / Collapse
Author
Message
Posted Thursday, January 27, 2011 12:46 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:21 AM
Points: 26, Visits: 117
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.
Post #1054859
Posted Friday, January 28, 2011 2:07 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:29 AM
Points: 646, Visits: 1,862
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'

Post #1055155
Posted Friday, January 28, 2011 9:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:21 AM
Points: 26, Visits: 117
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...
Post #1055432
Posted Wednesday, February 2, 2011 11:25 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,995, Visits: 31,516
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."

(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 #1057830
Posted Thursday, February 3, 2011 9:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
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?
Post #1058194
Posted Friday, February 4, 2011 6:47 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,995, Visits: 31,516
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."

(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 #1058695
Posted Thursday, May 10, 2012 3:27 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 10:51 AM
Points: 326, Visits: 2,219
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.
Post #1297706
Posted Thursday, May 10, 2012 7:18 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,995, Visits: 31,516
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."

(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 #1297856
Posted Thursday, May 10, 2012 7:39 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 10:51 AM
Points: 326, Visits: 2,219
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 :)

Kind regards,


Peter de Heer
Post #1297878
Posted Thursday, May 10, 2012 7:51 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,995, Visits: 31,516
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 :)

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

(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 #1297891
« Prev Topic | Next Topic »

Add to briefcase «««7891011»»»

Permissions Expand / Collapse