Formatting Dates with 3 Character Months (SQL Spackle)

Question

Formatting Dates with 3 Character Months (SQL Spackle)

Viewing 15 posts - 61 through 75 (of 108 total)

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

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

Anirban Paul (1/20/2011)
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!!!!!

Thanks for the feedback, Anirban. Do take a look at some of the international aspects people have posted in this discussion. Lots of folks came up with some pretty cool stuff.

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

ColdCoffee (1/20/2011)
Hey Jeff, thanks for article.. well written and full of information! Thanks a bunch! 🙂

Thanks for stopping by, CC. Make sure you read some of the discussions on the international aspects. Folks have done much better than I in that area.

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

UMG Developer (1/21/2011)
Thanks for another great piece of spackle!

Thanks, UMG. Like I've been telling folks, take a look at some of the posts on this thread concerning international short names. Lot's of smart people 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: 1004329 More actions · Answer 4

David McKinney (1/22/2011)
Well I didn't use your speciality, Jeff, the tally table...instead I used my speciality the CTE 😎
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

Nicely done but why do you need anything more than the following for any language which also has the added benefit of the year as sited in the article?

SELECT SUBSTRING(CONVERT(VARCHAR(30),GETDATE(),106),4,30)

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

To everyone,

Thank you for the wonderful discussion and all the code folks posted. Because I've not actually had to work on code that needs to be sensitive to language settings, I had a rather narrow vision of "short-month" conversions for languages other than English. Thanks to all of you, I've learned something new and have modified the original article to accommodate "short-month" names for other languages using the CONVERT method.

No... I didn't even try to make it "portable" code. To the best of my knowledge, it only works for T-SQL (SQL Server and, maybe, Sybase). I suppose many RDBMSs have the ability to change language and their individual date-formatting methods (which are usually quite different from T-SQL) can be warped into doing something similar as to what has been done in the article.

Again, thank all of you for the help and the great discussion. I love this community and I learn something new from it every day.

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

David McKinney SSChampion Points: 10472 More actions · Answer 6

Jeff, the question marks in your results come from the fact that you're using a varchar in your convert. Make it nvarchar and you should be alright.

CONVERT(nVARCHAR(30),GETDATE(....

David McKinney SSChampion Points: 10472 More actions · Answer 7

Nicely done but why do you need anything more than the following for any language which also has the added benefit of the year as sited in the article?
SELECT SUBSTRING(CONVERT(VARCHAR(30),GETDATE(),106),4,30)

Quite simply because I missed it in the article and discussion. Still, I learnt a new method of splitting comma separated values (new to me at least.) So it was time well spent.

Thanks for updating the article.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 8

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.

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

WayneS SSC Guru Points: 95460 More actions · Answer 9

Yet another article where the discussion is as valuable as the article itself! 🙂

Well done Jeff on (yet another) great article. And well done to all that posted enhancements.

Thank you all!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

nigel. SSChampion Points: 11636 More actions · Answer 10

WayneS (1/24/2011)
Yet another article where the discussion is as valuable as the article itself! 🙂

Couldn't agree more. Thanks Jeff, when's the next one? 😀

Nigel
How to post questions

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

WayneS (1/24/2011)
Yet another article where the discussion is as valuable as the article itself! 🙂
Well done Jeff on (yet another) great article. And well done to all that posted enhancements.
Thank you all!

Thanks for the feedback, Wayne. I appreciate your kind words.

BWAA-HAAA!!! Shifting gears... I almost always learn as much from the discussion for an article as I do from the article itself (even if it's mine). So far as I'm concerned, the very purpose of an article is to promote discussion on a given subject. 😀

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

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

Jeff Moden (1/25/2011)
WayneS (1/24/2011)
Yet another article where the discussion is as valuable as the article itself! 🙂
Well done Jeff on (yet another) great article. And well done to all that posted enhancements.
Thank you all!
Thanks for the feedback, Wayne. I appreciate your kind words.
BWAA-HAAA!!! Shifting gears... I almost always learn as much from the discussion for an article as I do from the article itself (even if it's mine). So far as I'm concerned, the very purpose of an article is to promote discussion on a given subject. 😀

Same for me Jeff, I never would have considdered finding alternate (faster) ways of retrieving a year or a month from a date, where it not for this article and the performance problem it presented. For me the use of this new knowledge goes well beyond the scope of this articles problem and its solutions.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 13

nigel. (1/25/2011)
WayneS (1/24/2011)
Yet another article where the discussion is as valuable as the article itself! 🙂
Couldn't agree more. Thanks Jeff, when's the next one? 😀

Hi Nigel. Thanks for the read and the enthusiasm. I really appreciate it. 🙂

I believe the next "SQL Spackle" article comes out this coming Thursday (2011-1-27). That one's about a really simple subject that isn't really a "frequent" problem but I expect that a lot of good folks will jump in with their own methods for handling the problem.

After that, I may actually need to take a couple of weeks off from writing (and {gasp!} maybe even posting) for SSC... I've got an SQLSaturday coming up and we have "lightning talks" at our next local PASS meeting that I've signed up to present a couple of. I also have to revisit the "Running Totals" article to add some new findings before it's republished in March. I'm also "gathering sticks" for a rather long article for SSC that I'd like to complete by mid March. Heh... it's no longer a matter of how many sticks I have in the fire... it's now a matter of how many fires I have sticks in. 😛

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

peter-757102 (1/25/2011)
Jeff Moden (1/25/2011)
WayneS (1/24/2011)
Yet another article where the discussion is as valuable as the article itself! 🙂
Well done Jeff on (yet another) great article. And well done to all that posted enhancements.
Thank you all!
Thanks for the feedback, Wayne. I appreciate your kind words.
BWAA-HAAA!!! Shifting gears... I almost always learn as much from the discussion for an article as I do from the article itself (even if it's mine). So far as I'm concerned, the very purpose of an article is to promote discussion on a given subject. 😀
Same for me Jeff, I never would have considdered finding alternate (faster) ways of retrieving a year or a month from a date, where it not for this article and the performance problem it presented. For me the use of this new knowledge goes well beyond the scope of this articles problem and its solutions.

Very cool feedback, Peter. THATs what I like people to get out of these "SQL Spackle" articles. As stated at the beginning of these types of articles, they may not provide a complete solution but they sure to seem to get good folks thinking.

Thanks again.

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

William Clardy SSChasing Mays Points: 627 More actions · Answer 15

[font="Courier New"]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)?[/font]