Formatting Dates with 3 Character Months (SQL Spackle)

Question

Formatting Dates with 3 Character Months (SQL Spackle)

Jeff Moden

SSC Guru

Points: 1004406
More actions
January 19, 2011 at 9:37 pm

#230662

Comments posted to this topic are about the item Formatting Dates with 3 Character Months (SQL Spackle)
--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)

Viewing 15 posts - 1 through 15 (of 108 total)

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

tommyh SSCertifiable Points: 6302 More actions · Answer 1

Have to say i dont really like the Convert version. There are as i see it multiple "problems" with it compared to DateName (the case version isnt really anything i would consider... unless you ALWAYS wanted the result back in a specific way regardless of language)

1. DateName helps describe what the code actually does. The Convert gives you nothing. Unless you know exactly what 106 will return and then you have to count to see what part it uses to know whats returned. Sure you can add a comment to remedy this... but you would have to remember to do that.

2. A change in requirements to return the full name of the month would mean that the Convert wont work at all. Use DateName and its just a change of the nr of chars returned (ergo remove the substring part)

3. A change in order (instead of "jan 2011" you want "2011 jan") is easier to handle with DateName. The Convert version would just be messy.

Now okay its faster in a specific situation at a price of readability/flexibility. Okay if your dealing with several billion rows then okay it might be worth it. But for more "normal" everyday usage... nah.

/T

David McKinney SSChampion Points: 10472 More actions · Answer 2

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?

David McKinney SSChampion Points: 10472 More actions · Answer 3

I did a little more hunting and I came across sys.syslanguages which looks quite promising.

To continue with the French exception, it has fields months and shortmonths with the comma separated values you see below.

janvier,février,mars,avril,mai,juin,juillet,août,septembre,octobre,novembre,décembre

janv,févr,mars,avr,mai,juin,juil,août,sept,oct,nov,déc

This merits consideration for a localised solution.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

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?

I knew there'd be at least one fly in the ointment. Considering those two months are so closely named, do you ever use 3 character months? If so, then I agree... a translation table may be better for the French language. Thanks for the feedback, David. I 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)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

tommyh (1/19/2011)
Have to say i dont really like the Convert version. There are as i see it multiple "problems" with it compared to DateName (the case version isnt really anything i would consider... unless you ALWAYS wanted the result back in a specific way regardless of language)
1. DateName helps describe what the code actually does. The Convert gives you nothing. Unless you know exactly what 106 will return and then you have to count to see what part it uses to know whats returned. Sure you can add a comment to remedy this... but you would have to remember to do that.
2. A change in requirements to return the full name of the month would mean that the Convert wont work at all. Use DateName and its just a change of the nr of chars returned (ergo remove the substring part)
3. A change in order (instead of "jan 2011" you want "2011 jan") is easier to handle with DateName. The Convert version would just be messy.
Now okay its faster in a specific situation at a price of readability/flexibility. Okay if your dealing with several billion rows then okay it might be worth it. But for more "normal" everyday usage... nah.
/T

True enough. You won't see benefits of the CONVERT method unless you deal with mega-rows on a regular basis. Just so long as you're not using the CASE statement I posted, you should be all set. Thanks for the feedback.

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

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

Different method

DECLARE @Bitbucket CHAR(10);

SET STATISTICS TIME ON;

SELECT @Bitbucket = substring(convert(varchar(100), SomeDate,13) , 4,8)

FROM dbo.JBMTest

SET STATISTICS TIME OFF;

and results:

CPU time = 1078 ms, elapsed time = 1073

Tiempo de CPU = 1047 ms, tiempo transcurrido = 1042 ms.

CPU-Zeit = 1062 ms, verstrichene Zeit = 1056 ms.

surreydude. SSChasing Mays Points: 601 More actions · Answer 7

Interesting article. Mal-formed dates are the bane of my professional life; often this goes hand-in-hand with Excel data and/or lack of user discipline and validation.

I've experienced date-related issues whilst working around the globe and in my experience, the US has the worst practices, whilst continental Europe has better disciplined users and validation. Whenever I am involved in the design/ETL phase of a project concerning data with dates, I go that bit extra to ensure that we have a least understood the magnitude of the problems around storage of such data. I favour always referencing dates in full ISO format, but it is important that people understand the format being used, rather than assume based on their locale. Where ambiguity can sneak in, I will use a format that is explicit and localised.

One thing I did notice about the article, however, was the use of yy for year formats. It would seem that the "Y2K Bug" has not taught us very much afterall!

katesl SSCommitted Points: 1743 More actions · Answer 8

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.

_________________
"Look, those sheep have been shorn."
data analyst replies, "On the sides that we can see.."

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 9

Whupty-freakin'-doo! This is an amazing article!

I have never heard of DATENAME before, so thanks for pointing it out Jeff.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 10

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.

This is quite bizarre.

The first time I couldn't copy paste either from the article to test things out.

But when I opened the article for a second time, I could copy paste everything.

Weird IE problem?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

nigel. SSChampion Points: 11636 More actions · Answer 11

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.

It gets even worse for languages such as Finnish, Estonian, & Czech (to name but a few).

For instance Czech uses the Roman numerals (I,II,III,IV etc.) for short month names, and in Estonian the short month names vary in length from 3 to 5 characters.

Nigel
How to post questions

Phil Factor SSC-Insane Points: 20244 More actions · Answer 12

Yes, the correct shortnames for the months are listed in sys.syslanguages

If you run this code, it should show you how, in SQL Server, all the languages format the current date using the date format code 113. 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!

[font="Courier New"]DECLARE @TestEmOut NVARCHAR(MAX)

CREATE TABLE #ShortMonthNames (NAME NVARCHAR(20), Alias NVARCHAR(30), Date NVARCHAR(30), ShortMonthNames NVARCHAR(255))

SELECT @TestEmOut=''

SELECT @TestEmOut= @TestemOut+'

Set language N'''+name+'''

insert into #shortMonthNames (Name, Alias, Date, ShortMonthNames)

SELECT N'''+name+''',N'''+alias+''', CONVERT(nVARCHAR(80),GetDate(),113),

(Select shortmonths from sys.syslanguages where name like N'''+NAME+''')'

FROM sys.syslanguages

EXEC(@TestEmOut)

SELECT * FROM #ShortMonthNames

DROP TABLE #ShortMonthNames[/font]

(Edited after David McKinney's help with setting the language to Brasilian portugese. Oops!)

Best wishes,
Phil Factor

WayneS SSC Guru Points: 95460 More actions · Answer 13

surreydude. (1/20/2011)
One thing I did notice about the article, however, was the use of yy for year formats. It would seem that the "Y2K Bug" has not taught us very much afterall!

The only place where "yy" is being used in the article is in the DateName(yy, ) parts. In this, the "yy" (or "yyyy") is just the abbreviation for "year" - using any of these returns the full year (all four characters). See the DateName BOL - specifically look at the datepart chart in the Arguments section. And in the Return Value section is this:

Each datepart and its abbreviations return the same value.

So, would this be myth... busted?

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

David McKinney SSChampion Points: 10472 More actions · Answer 14

WHERE name NOT IN ('Português (Brasil)'

Thanks Phil.

Putting the language in quotes will solve the Portuguese Brazil problem

Set language ['+name+']....