Formatting Dates with 3 Character Months (SQL Spackle)

Question

Formatting Dates with 3 Character Months (SQL Spackle)

Viewing 15 posts - 31 through 45 (of 108 total)

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

Jeff Moden SSC Guru Points: 1003907 More actions · Answer 1

bitbucket-25253 (1/20/2011)
I use a "throw away" variable (@BitBucket)
BWAA-HAA!!!
Hmmmm
The quotes aside .. Jeff a great article with more sample code than most of us deserve.
Thanks, as I put this into my personal "toolbox"

Heh... now that's funny, Ron. I flat forgot that's your handle here. 🙂

--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: 1003907 More actions · Answer 2

alen teplitsky (1/20/2011)
is this a SQL 2008 only function or is it in SQL 2005 as well?

I believe that it'll work in any version of Standard or Enterprise editions. If you have 2005, give it a try.

--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: 1003907 More actions · Answer 3

carie dobson (1/20/2011)
Well written AND entertaining. Nice. 🙂
I've been using SqlSvr since the 6.5 days I still learn new stuff everyday. I have to admit I usually use DatePart - mostly because I had forgotten that DateName existed.
So thanks for the reminder, Jeff. Hope you'll write some more articles in the future.

Thanks for the feedback, Carie.

--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: 1003907 More actions · Answer 4

katesl (1/20/2011)
Question from a DBA--
I have to ask this question because I have no experience doing user interface programming-- I use T-SQL and SSMS, and Excel to deliver reports, for everything I do. In the article, the T-SQL is presented in a window with horizontal scroll bars. What is the name by which to refer to such a window? It does not allow copy. Is this a problem of the particular browser I'm using or is it by design? With less elaborate user interface, I was able to copy the code from the article and paste it into the SSMS query window. Why is this scroll bar window supposed to be better than what worked just fine?
Thank you.

The code windows are a bit fickle. I usually tell people to select from the last couple of characters just before the code window to the first couple of characters outside the code window. Then it copies pretty well (as least for IE).

--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: 1003907 More actions · Answer 5

Koen (da-zero) (1/20/2011)
Whupty-freakin'-doo! This is an amazing article!
I have never heard of DATENAME before, so thanks for pointing it out Jeff.

You bet Koen... thanks for the feedback.

As a side bar, I'll have to modify the article a bit because of the good folks that provided feedback on the international month names.

--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: 1003907 More actions · Answer 6

radyo (1/20/2011)
My experience are just opposite. DATENAME is slower than CONVERT + DATEPART. See below:
/*------------------------
DECLARE @Bitbucket CHAR(4);
PRINT '========== DATENAME Method of Conversion ==========';
SET STATISTICS TIME ON;
SELECT @Bitbucket = DATENAME(yy,SomeDate)
FROM dbo.JBMTest
SET STATISTICS TIME OFF;
PRINT '========== Original Method of Conversion CONVERT ==========';
SET STATISTICS TIME ON;
SELECT @Bitbucket = CONVERT(CHAR(4), DATEPART(yy,SomeDate))
FROM dbo.JBMTest;
SET STATISTICS TIME OFF;
PRINT '========== Original Method of Conversion CAST ==========';
SET STATISTICS TIME ON;
SELECT @Bitbucket = CAST(DATEPART(yy,SomeDate) AS CHAR(4))
FROM dbo.JBMTest;
SET STATISTICS TIME OFF;
------------------------*/
========== DATENAME Method of Conversion ==========
SQL Server Execution Times:
CPU time = 703 ms, elapsed time = 718 ms.
========== Original Method of Conversion CONVERT ==========
SQL Server Execution Times:
CPU time = 562 ms, elapsed time = 578 ms.
========== Original Method of Conversion CAST ==========
SQL Server Execution Times:
CPU time = 579 ms, elapsed time = 579 ms.

Thanks for the post and the test, radyo. Proof positive that "It depends" lives everywhere. What is the configuration of your machine? Mine is a 9 year old, single 1.8GHz P4 with a Gig of ram running 2k5 Developers Edition SP3 over XP SP3 (yeah... I know... but most of my work-work is done in 2k5 for now and the machine hasn't failed me yet).

--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: 1003907 More actions · Answer 7

sharath.chalamgari (1/20/2011)
Thanks Jeff for the article. i learned something about the datename function and these langauages settings.

Thanks for the feedback Sharath. As so often is the case, I learned something from the discussion, as well. There's a lot of good people on this site.

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

Ken_Fowler Grasshopper Points: 23 More actions · Answer 8

thanks for pointing out this great SQL function, I had not been aware of it prior to this article. I do believe though that much of the performance gain you are seeing in you example is due to caching of the data rather that any inherent performance gain in the methodology. When I execute the queries in the order you give I get similar results but if the order is reversed I get significantly different results.

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

Phil Factor (1/20/2011)
You'll see that the shortmonthname is correctly inserted into the date if you use CONVERT. Is there a way of using DateName to get the correct short-date? I haven't discovered it!

Thanks for the confirmation, Phil. Now I don't have to write code to test it. Always a pleasure, Sir! 🙂

--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: 1003907 More actions · Answer 10

allmhuran (1/20/2011)
"You mean an 8% increase in performance with 44% less typing? Don't you wish you could do that everywhere?"
SOLD!

Heh... I agree! Thanks for stopping by. 🙂

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

nigel. SSChampion Points: 11636 More actions · Answer 11

I think this should cope with the language issues:

SELECT SUBSTRING(CONVERT(CHAR(13),SomeDate,106),4,13)

FROM dbo.JBMTest

Is marginally slower than Jeffs original solution (on my box), but seems to cope with short month names that are not 3 chars.

Nigel
How to post questions

mtassin SSC-Insane Points: 23099 More actions · Answer 12

David McKinney (1/20/2011)
Très bien, Jeff! Except of course that you can't keep everybody happy!
In France, June and July are Juin and Juillet respectively, and hence JUI and JUI when truncated to 3 characters.
Some would say I'm just being pedantic - and they would be right - but it's more important to me, who lives in France, than to most of you who don't 😉
I'd maybe consider a translations table?

convert also does languages

SET LANGUAGE 'French'

SELECT CONVERT(VARCHAR(100),GETDATE(),106)

SET LANGUAGE 'US_English'

SELECT CONVERT(VARCHAR(100),GETDATE(),106)

Returns

20 janv 2011

20 Jan 2011

What about

instead

SET LANGUAGE 'French'

SELECT

REVERSE(LEFT(

REVERSE(

CONVERT(VARCHAR(100),GETDATE(),106)),

CHARINDEX(' ',CONVERT(VARCHAR(100),GETDATE(),106),

CHARINDEX(' ',CONVERT(VARCHAR(100),GETDATE(),106))+1)+1))

SET LANGUAGE 'US_English'

SELECT

REVERSE(LEFT(

REVERSE(

CONVERT(VARCHAR(100),GETDATE(),106)),

CHARINDEX(' ',CONVERT(VARCHAR(100),GETDATE(),106),

CHARINDEX(' ',CONVERT(VARCHAR(100),GETDATE(),106))+1)+1))

At this point though, using DATENAME probably is faster.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

marcin.motyl SSC Enthusiast Points: 119 More actions · Answer 13

Yes. I ran query 3 times with different language settings. Result are rendered against your test data.

I think, that my test environment is far weaker;).

Forgive me, but I will no prepare test cases nor try to beat scores ,but feel free to test this query /"solution" against any data you want.

Jeff Moden SSC Guru Points: 1003907 More actions · Answer 14

marcin.motyl (1/20/2011)
Yes. I ran query 3 times with different language settings. Result are rendered against your test data.
I think, that my test environment is far weaker;).
Forgive me, but I will no prepare test cases nor try to beat scores ,but feel free to test this query /"solution" against any data you want.

Not sure why you posted your "scores" then. 😉 Thanks for the code though.

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

mtassin SSC-Insane Points: 23099 More actions · Answer 15

Finally got a chance to use Jeff's test data.

This seems to work for french, I'd expect that it would work for any language that uses convert 106 to put dd mmm yyyy data.

It's as slow as the first one... sadly... but it seems to work in other languages

PRINT '============ CONVERT to handle foreign languages =========';

SET STATISTICS TIME ON;

SELECT

@Bitbucket =

RIGHT(CONVERT(VARCHAR(100),somedate,106),LEN(CONVERT(VARCHAR(100),somedate,106)) - CHARINDEX(' ',CONVERT(VARCHAR(100),somedate,106)))

FROM dbo.JBMTest;

SET STATISTICS TIME OFF;

Here's what I get on my local sql machine. It's slow... but it works no matter the language I set it to so far.

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

SQL Server Execution Times:

CPU time = 2203 ms, elapsed time = 2202 ms.

========== DATENAME Method of Conversion ==========

SQL Server Execution Times:

CPU time = 1156 ms, elapsed time = 1195 ms.

========== CONVERT Method of Conversion ==========

SQL Server Execution Times:

CPU time = 1031 ms, elapsed time = 1029 ms.

============ CONVERT to handle foreign languages =========

SQL Server Execution Times:

CPU time = 2453 ms, elapsed time = 2573 ms.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]