Formatting Dates with 3 Character Months (SQL Spackle)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Jeff Moden » Formatting Dates with 3 Character Months (SQL...

92 posts, Page 6 of 10

«««4 5 6 7 8 »»»

Formatting Dates with 3 Character Months (SQL Spackle)

Rate Topic

Display Mode

Topic Options

Author

Message

Anipaul

Posted Thursday, January 20, 2011 11:21 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:03 AM
Points: 4,786, Visits: 1,335

I nowadays do the original conversion like a mechnical machine. Just type those 10-15 lines to convert the date. Thanks Jeff I will now switch over to Datename. Great writing man!!!!!

Post #1051345

ColdCoffee

Posted Thursday, January 20, 2011 11:26 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:37 PM
Points: 2,248, Visits: 5,352

Hey Jeff, thanks for article.. well written and full of information! Thanks a bunch! Smile

Post #1051346

peter-757102

Posted Friday, January 21, 2011 3:36 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 3:04 AM
Points: 287, Visits: 1,901

Thanks again Jeff for the discussions you always seem to bring up among us.

Time to put in a few thoughts and findings of my own. As someone already observed, DATENAME is a rather slow function on more modern machines. It just shows that the best model to do benchmark against is something that is similar to where the code is expected to eventually run on.

Here is a common fast technique for looking up fixed length substrings (as is with short month names in most languages):

SELECT @BitBucket = subString( 'Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec ', month( SomeDate ) * 4 - 3, 3 )

This will return a three letter short month name very fast. Now subsequent string concatenations can slow it down a bit, but if a month is all you need, this one will be hard (if not impossible) to beat. Also note that substituting the last argument (3) with (4) gives you a free concatenated space at the end of the month. The same sort of trick you can use to prefix a month with a space or a space and a comma. While fast on its own, subsequent string operations, such as adding a year as text will slow it down again. Also note that in case of variable length substrings names, it is still cheap to pad them with spaces to make them all the same length and then do a right trim (rtrim) over it.

I also took a look at the quite interesting sys.syslanguages table, but what i miss there is list of short months compatible with my above requirement. That said, it isn’t hard at all to derive a customized version of that table with only the stuff you expect to use. Another good option in my opinion is the use of calendar tables where you can store short representation texts in a denormalized way and retrieve it very fast. Granted, you miss the multi-language aspect, but I never really needed that in my work anyway.

Post #1051412

peter-757102

Posted Friday, January 21, 2011 4:44 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 3:04 AM
Points: 287, Visits: 1,901

I did some more testing and as it turns out, much of the cost in slower solutions is in dissecting a date multiple times and/or using an inefficient method. And as often in SQL, there are many ways to skin a cat, each with different performance characteristics.

/* Five methods to get a year as text from a date */
select convert( char(4), getDate(), 112 )
select datename( yyyy, getDate() ) 
select cast( datepart( year, getDate() ) as char(4) )
select cast( year( getDate() ) as char(4) )
select cast( 1900 + dateDiff( year, 0, getDate() ) as char(4) )

Note the use of format 112 in the above convert, it is a deterministic format, so can be used in deterministic functions.

As it turns out, the last and reletivly complex is the fastest of all and thus even beats the specialised 'datepart' and 'year' functions at their own game. I suspect dateDiff uses an optimised code path, whereas datepart first disects the whole datetime and then takes the date part. The year function is problably nothing more then a wrapper and is internally converted into a datepart.

Here are my timings (test code first, followed by results):

DECLARE @BitBucket  CHAR(8),
        @NBitBucket NCHAR(8)
;

  PRINT '========== Original Method of Conversion ==========';
    SET STATISTICS TIME ON;
 SELECT @BitBucket = CASE DATEPART(mm,SomeDate) 
            WHEN 01 THEN 'Jan'
            WHEN 02 THEN 'Feb'
            WHEN 03 THEN 'Mar'
            WHEN 04 THEN 'Apr'
            WHEN 05 THEN 'May'
            WHEN 06 THEN 'Jun'
            WHEN 07 THEN 'Jul'
            WHEN 08 THEN 'Aug'
            WHEN 09 THEN 'Sep'
            WHEN 10 THEN 'Oct'
            WHEN 11 THEN 'Nov'
            ELSE 'Dec'
        END + ' ' + CAST(DATEPART(yy,SomeDate) AS CHAR(4))
   FROM dbo.JBMTest;
    SET STATISTICS TIME OFF;

  PRINT '========== DATENAME Method of Conversion ==========';
    SET STATISTICS TIME ON;
 SELECT @NBitBucket = SUBSTRING(DATENAME(mm,SomeDate),1,3) + N' ' + DATENAME(yy,SomeDate)
   FROM dbo.JBMTest;
    SET STATISTICS TIME OFF;

  PRINT '========== CONVERT Method of Conversion ==========';
    SET STATISTICS TIME ON;
 SELECT @BitBucket = RIGHT(CONVERT(CHAR(11),SomeDate,106),8)
   FROM dbo.JBMTest;
   SET STATISTICS TIME OFF;

  PRINT '========== Substring + year() Method of Conversion ==========';
    SET STATISTICS TIME ON;
 SELECT @BitBucket = subString( 'Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec ', month( SomeDate ) * 4 - 3, 4 ) + cast( year( SomeDate ) as char(4) )
   FROM dbo.JBMTest;
   SET STATISTICS TIME OFF;

  PRINT '========== Substring + datepart( year ) Method of Conversion ==========';
    SET STATISTICS TIME ON;
 SELECT @BitBucket = subString( 'Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec ', month( SomeDate ) * 4 - 3, 4 ) + cast( datepart( year, SomeDate ) as char(4) )
   FROM dbo.JBMTest;
   SET STATISTICS TIME OFF;

  PRINT '========== Substring + ISO convert of year Method of Conversion ==========';
    SET STATISTICS TIME ON;
 SELECT @BitBucket = subString( 'Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec ', month( SomeDate ) * 4 - 3, 4 ) + convert( char(4), SomeDate, 112 )
   FROM dbo.JBMTest;
   SET STATISTICS TIME OFF;

  PRINT '========== Substring + datediff (generally fastest) Method of Conversion ==========';
    SET STATISTICS TIME ON;
 SELECT @BitBucket = subString( 'Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec ', month( SomeDate ) * 4 - 3, 4 ) + cast( 1900 + dateDiff( year, 0, SomeDate ) as char(4) )
   FROM dbo.JBMTest;
   SET STATISTICS TIME OFF;

========== Original Method of Conversion ==========

 SQL Server Execution Times:
   CPU time = 1453 ms,  elapsed time = 1463 ms.
========== DATENAME Method of Conversion ==========

 SQL Server Execution Times:
   CPU time = 1031 ms,  elapsed time = 1030 ms.
========== CONVERT Method of Conversion ==========

 SQL Server Execution Times:
   CPU time = 750 ms,  elapsed time = 736 ms.
========== Substring + year() Method of Conversion ==========

 SQL Server Execution Times:
   CPU time = 750 ms,  elapsed time = 752 ms.
========== Substring + datepart( year ) Method of Conversion ==========

 SQL Server Execution Times:
   CPU time = 750 ms,  elapsed time = 751 ms.
========== Substring + ISO convert of year Method of Conversion ==========

 SQL Server Execution Times:
   CPU time = 954 ms,  elapsed time = 954 ms.
========== Substring + datediff (generally fastest) Method of Conversion ==========

 SQL Server Execution Times:
   CPU time = 718 ms,  elapsed time = 723 ms.

It makes me wonder if month( somedate ) can also be optimised as I did with year( somedate )

Post #1051435

peter-757102

Posted Friday, January 21, 2011 5:05 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 3:04 AM
Points: 287, Visits: 1,901

I DISCOVERED AN ERROR IN MY CODE HERE, SKEWING THE RESULTS, PLEASE IGNORE THIS PART OF MY CONTRIBUTION

(In one of the two datediffs, I used getDate() instead of "SomeDate" that is read from the large table of testdata)

As I suspected, it can be optimised further by discarding all use of datepart, month, year and convert functions.

The new testcode (only included original and fastest running versions)

DECLARE @BitBucket  CHAR(8),
        @NBitBucket NCHAR(8)
;

  PRINT '========== Original Method of Conversion ==========';
    SET STATISTICS TIME ON;
 SELECT @BitBucket = CASE DATEPART(mm,SomeDate) 
            WHEN 01 THEN 'Jan'
            WHEN 02 THEN 'Feb'
            WHEN 03 THEN 'Mar'
            WHEN 04 THEN 'Apr'
            WHEN 05 THEN 'May'
            WHEN 06 THEN 'Jun'
            WHEN 07 THEN 'Jul'
            WHEN 08 THEN 'Aug'
            WHEN 09 THEN 'Sep'
            WHEN 10 THEN 'Oct'
            WHEN 11 THEN 'Nov'
            ELSE 'Dec'
        END + ' ' + CAST(DATEPART(yy,SomeDate) AS CHAR(4))
   FROM dbo.JBMTest;
    SET STATISTICS TIME OFF;
  select @BitBucket;

  PRINT '========== CONVERT Method of Conversion ==========';
    SET STATISTICS TIME ON;
 SELECT @BitBucket = RIGHT(CONVERT(CHAR(11),SomeDate,106),8)
   FROM dbo.JBMTest;
   SET STATISTICS TIME OFF;
  select @BitBucket;

  PRINT '========== Substring + datediff for year Method of Conversion ==========';
    SET STATISTICS TIME ON;
 SELECT @BitBucket = subString( 'Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec ', month( SomeDate ) * 4 - 3, 4 ) + cast( 1900 + dateDiff( year, 0, SomeDate ) as char(4) )
   FROM dbo.JBMTest;
   SET STATISTICS TIME OFF;
  select @BitBucket;

  PRINT '========== Substring + (datediff for all date processing) Method of Conversion ==========';
    SET STATISTICS TIME ON;
 SELECT @BitBucket = subString( 'Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec ', ( dateDiff( month, 0, getDate() ) % 12 + 1 ) * 4 - 3, 4 ) + cast( 1900 + dateDiff( year, 0, SomeDate ) as char(4) )
   FROM dbo.JBMTest;
   SET STATISTICS TIME OFF;
  select @BitBucket;

========== Original Method of Conversion ==========

 SQL Server Execution Times:
   CPU time = 1640 ms,  elapsed time = 1618 ms.
========== CONVERT Method of Conversion ==========

 SQL Server Execution Times:
   CPU time = 750 ms,  elapsed time = 751 ms.
========== Substring + datediff for year Method of Conversion ==========

 SQL Server Execution Times:
   CPU time = 703 ms,  elapsed time = 724 ms.
========== Substring + (datediff for all date processing) Method of Conversion ==========

 SQL Server Execution Times:
   CPU time = 547 ms,  elapsed time = 555 ms.

The lastest code is no less then 3 times faster then the original, now that is progress :)

The big lesson for me in all this is to be shy of the datepart and similar functions, avoid them whenever you can!

Post #1051446

Jeff Moden

Posted Friday, January 21, 2011 8:14 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:33 PM
Points: 32,902, Visits: 26,783

Very cool, Peter! Thanks for the coding, the testing, and the hypothesis! I'll do some additional testing over the weekend.

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1051575

David McKinney

Posted Friday, January 21, 2011 10:11 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:43 AM
Points: 582, Visits: 1,601

Jeff,

I'm waiting for you to blow us away with your tally table solution to pull the correct month from the comma separated field in sys.syslanguages.

Or will I have to try to do it myself? w00t

Post #1051679

UMG Developer

Posted Friday, January 21, 2011 2:55 PM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 11:21 AM
Points: 2,163, Visits: 2,148

Thanks for another great piece of spackle!

Post #1051860

Jeff Moden

Posted Friday, January 21, 2011 4:24 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:33 PM
Points: 32,902, Visits: 26,783

David McKinney (1/21/2011)

Jeff,

I'm waiting for you to blow us away with your tally table solution to pull the correct month from the comma separated field in sys.syslanguages.

Or will I have to try to do it myself? w00t

Heh... thanks for the confidence, David, but I believe the Tally Table solution would be slower than Peter's good method. I wish it were otherwise but the Tally Table isn't a panacea. Smile

--Jeff Moden

Post #1051886

David McKinney

Posted Saturday, January 22, 2011 2:25 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:43 AM
Points: 582, Visits: 1,601

Well I didn't use your speciality, Jeff, the tally table...instead I used my speciality the CTE Cool

I was mucking about with recursive CTEs (actually I rather like them at the moment)
and ended up constructing a view which converts the shortmonths column of sys.syslanguages into something useful. If you don't want the view just try the select.

The view could be used as the basis of a solution which would work for all languages.

Hope you like it!

CREATE VIEW ShortMonths as

With Months as
(select sll.lcid,sll.shortmonths,1 as MonthNumber, 1 as Position FROM sys.syslanguages sll
UNION ALL
select sl.lcid, sl.shortmonths,m.MonthNumber+1, CHARINDEX(',',sl.shortmonths, m.Position+1)+1 from sys.syslanguages sl
inner join Months m on m.lcid=sl.lcid
where  CHARINDEX(',',sl.shortmonths, m.Position+1)<>0
),
ShowMonths as
(select m1.lcid,m1.MonthNumber,m1.shortmonths,m1.Position as StartPosition, isnull(m2.Position-1,len(m1.shortmonths)+1) as EndPosition FROM Months m1 left join Months m2 on m1.MonthNumber=m2.MonthNumber-1 and m1.lcid=m2.lcid)


select sm.lcid, SUBSTRING(sm.shortmonths,sm.StartPosition,sm.EndPosition-sm.StartPosition) as ShortMonth,sm.MonthNumber FROM ShowMonths sm

Post #1051994

« Prev Topic | Next Topic »

92 posts, Page 6 of 10

«««4 5 6 7 8 »»»

Permissions