Formatting Dates with 3 Character Months (SQL Spackle)

Question

Formatting Dates with 3 Character Months (SQL Spackle)

Viewing 15 posts - 91 through 105 (of 108 total)

You must be logged in to reply to this topic. Login to reply

peter-757102 SSCertifiable Points: 6877 More actions · Answer 1

Jeff,

With runaway generation of rows I do not mean these rows end up in the result or are part of additional processing by the consuming parts of the query. But that rows are generated before the TOP operator takes effect when dealing with parallel execution plans. Not always, but frequent enaugh to cause issues of unpredictable large slowdowns. The processing is in the generation of the rows itself for large sets and not anything that depends on the rows that come out of the top operator.

It is a bug obviously and one that the connect issue can use an extra votes for 🙂

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

Wow! I just read Paul's article. He really does do a nice job explaining some of these things.

To summarize, TOP apparently doesn't fix things for 2008 and above. You need to add OPTION(MAXDOP 1) to really get it to behave properly. That's ok because it still screams.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

fregatepallada SSC-Addicted Points: 407 More actions · Answer 3

Guys, did somebody mention this 😉 (valid only in SQL Server 2012):

-- Current date

DECLARE @d DATETIME = GETDATE();

SELECT FORMAT( @d, 'dd MMM yyyy', 'en-US' ) AS 'DateTime Result'

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 4

fregatepallada (7/19/2013)
Guys, did somebody mention this 😉 (valid only in SQL Server 2012):
-- Current date
DECLARE @d DATETIME = GETDATE();
SELECT FORMAT( @d, 'dd MMM yyyy', 'en-US' ) AS 'DateTime Result'

Thank you! I was about to mention the format() function as well. 😀

If all you want is the month, you can use MMM or Mmm as the format string. Plus you can change locales!

Of course, the caveat is that it doesn't work in versions of SQL Server prior to 2012. Sadly, that rules it out for most of what I do. The good news is that we BI people pre-build date dimensions with all of that stuff anyway, so I only ever have to worry about this sort of thing when actually building a date dimension. And of course, the general rule is that in almost every case, return a date as a date and let the front end application do the formatting.

Geoff A SSChampion Points: 11511 More actions · Answer 5

fregatepallada (7/19/2013)
Guys, did somebody mention this 😉 (valid only in SQL Server 2012):
-- Current date
DECLARE @d DATETIME = GETDATE();
SELECT FORMAT( @d, 'dd MMM yyyy', 'en-US' ) AS 'DateTime Result'

that method, while super easy to read and understand, is super slow.

using the FORMAT function on my laptop, the results are sorta bad....

PRINT '========== Format Methed of Conversion ==========';

SET STATISTICS TIME ON;

SELECT @Bitbucket = FORMAT(SomeDate, 'MMM yyyy', 'en-US' )

FROM dbo.JBMTest

SET STATISTICS TIME OFF;

========== Format Methed of Conversion ==========

SQL Server Execution Times:

CPU time = 25148 ms, elapsed time = 26425 ms.

Junie01 Say Hey Kid Points: 694 More actions · Answer 6

The other advantage of using datename for days of the week, is that you don't have to remember if 0 is Sun or Mon etc ! (or set it at the top of the code)

fregatepllada SSCommitted Points: 1700 More actions · Answer 7

Geoff A (7/19/2013)
fregatepallada (7/19/2013)
Guys, did somebody mention this 😉 (valid only in SQL Server 2012):
-- Current date
DECLARE @d DATETIME = GETDATE();
SELECT FORMAT( @d, 'dd MMM yyyy', 'en-US' ) AS 'DateTime Result'
that method, while super easy to read and understand, is super slow.
using the FORMAT function on my laptop, the results are sorta bad....
PRINT '========== Format Methed of Conversion ==========';
SET STATISTICS TIME ON;
SELECT @Bitbucket = FORMAT(SomeDate, 'MMM yyyy', 'en-US' )
FROM dbo.JBMTest
SET STATISTICS TIME OFF;
========== Format Methed of Conversion ==========
SQL Server Execution Times:
CPU time = 25148 ms, elapsed time = 26425 ms.

Jeff let's compare apples with apples - just remove select value from table for starters !

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 8

Hi Jeff,

Another useful article, Thank You!

I repeated your work on my machine and the improvement was between 1.5 and 1.7 times.

Regards,

IgorMi

Igor Micev,My blog: www.igormicev.com

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

Irozenberg 1347 (7/19/2013)
Geoff A (7/19/2013)
fregatepallada (7/19/2013)
Guys, did somebody mention this 😉 (valid only in SQL Server 2012):
-- Current date
DECLARE @d DATETIME = GETDATE();
SELECT FORMAT( @d, 'dd MMM yyyy', 'en-US' ) AS 'DateTime Result'
that method, while super easy to read and understand, is super slow.
using the FORMAT function on my laptop, the results are sorta bad....
PRINT '========== Format Methed of Conversion ==========';
SET STATISTICS TIME ON;
SELECT @Bitbucket = FORMAT(SomeDate, 'MMM yyyy', 'en-US' )
FROM dbo.JBMTest
SET STATISTICS TIME OFF;
========== Format Methed of Conversion ==========
SQL Server Execution Times:
CPU time = 25148 ms, elapsed time = 26425 ms.
Jeff let's compare apples with apples - just remove select value from table for starters !

What do you mean "remove select value from table"? Why wouldn't you use this against a table?

Geoff (the other "Jeff"), did the exact same test as what's in the article which contains a million rows. I'd say he did a pretty good job of comparing "apples-to-apples". I did the original test in the article on an 11 year old single CPU desktop, which is MUCH slower than any modern day laptop, and the "new" 2012 method is still 10 times slower according to Geoff's tests.

I will admit though... both of us used SET STATISTICS to get our times which has been known to make a mess of things when testing Scalar Functions in T-SQL. Perhaps the new FORMAT function has the same problems as Scalar Functions written in T-SQL. I don't have 2012 loaded on any of my boxes at home or at work (explains why I didn't load it at home), yet. I do have a vacation starting tomorrow. Maybe I'll have time to load 2012 on my laptop during the vacation.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 10

Geoff A (7/19/2013)
fregatepallada (7/19/2013)
Guys, did somebody mention this 😉 (valid only in SQL Server 2012):
-- Current date
DECLARE @d DATETIME = GETDATE();
SELECT FORMAT( @d, 'dd MMM yyyy', 'en-US' ) AS 'DateTime Result'
that method, while super easy to read and understand, is super slow.
using the FORMAT function on my laptop, the results are sorta bad....
PRINT '========== Format Methed of Conversion ==========';
SET STATISTICS TIME ON;
SELECT @Bitbucket = FORMAT(SomeDate, 'MMM yyyy', 'en-US' )
FROM dbo.JBMTest
SET STATISTICS TIME OFF;
========== Format Methed of Conversion ==========
SQL Server Execution Times:
CPU time = 25148 ms, elapsed time = 26425 ms.

Wow! Unless SET STATISTICS is really putting the hammer on this like it does on Scalar Functions (see the following article on that subject), it looks like MS may have might have made a terrible mistake in their code. That's really too bad because a lot of people were really looking forward to this FORMAT functionality.

Hat's off to you for being one of those good people that actually tests the new stuff instead of just blindly using it. Hmmmm... I wonder if the formatting in SSIS has the same massive performance problem as this. It's ultra rare to find anyone that's doing performance tests in SSIS. I don't test in SSIS because I avoid using it altogether. 😀

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 11

IgorMi (7/19/2013)
Hi Jeff,
Another useful article, Thank You!
I repeated your work on my machine and the improvement was between 1.5 and 1.7 times.
Regards,
IgorMi

Thank you for the great feedback and the timing difference, Igor. Glad the article could help.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

j-1064772 SSCertifiable Points: 5316 More actions · Answer 12

Jeff:

Yet another sterling article!

I do not even need all the fingers on one hand to count the authors I have occasionally corresponded with.

You definitely stand out among the crowd.

And still amazed a your total of 33,313 points acquired in what 10 ? 20 ? years.

Koen:

Neither did I. 12 years writing SQL statements for reports and DATENAME() had completely escaped me.

Learn something every day !

David:

In Québec we also need to write dates in French, thanks for your input. Nothing pedantic here.

My customers accept Jun and Jul for three-letter month names when column width must be kept to a minimum.

j-1064772 SSCertifiable Points: 5316 More actions · Answer 13

In Québec, we have to daily contend with two date formats mm-dd-yyyy (US English) and dd-mm-yyyy (French). I have repeatedly seen users confusing April 1 with January 4. So much that when I print a date column in a report I always add the date format below the column name in a table column's header.

I have found that the only format that cannot possibly be misunderstood by anyone is

yyyy-mm-dd. But sometimes you have to live with the customers' own way of doing things.

And I use the hyphen instead of the slash because I find it more legible.

Miles Neale SSChampion Points: 13147 More actions · Answer 14

I had not seen this previously when it was posted on the site. It is great -> Thanks!

Not all gray hairs are Dinosaurs!

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 15

j-1064772 (7/19/2013)
Jeff:
Yet another sterling article!
I do not even need all the fingers on one hand to count the authors I have occasionally corresponded with.
You definitely stand out among the crowd.
And still amazed a your total of 33,313 points acquired in what 10 ? 20 ? years.

Heh... and some of my posts are actually useful. 😀

I started posting regularly on SQLServerCentral.com back in 2003 or so because they closed down my old favorite (Belution.com). I'm glad it happened because this is one of the best communities of people dedicated to the arts of SQLServer that I've ever seen. I thank Steve Jones and the other plank owners of this site every day.

I used to be the leading poster but Gail Shaw passed me up a couple of years ago and I've never been able to catch up. She's incredible.

Thank you very much for the feedback on this article. I really appreciate it.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)