Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    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...
Add to briefcase
92 posts, Page 1 of 10
12345»»»

Formatting Dates with 3 Character Months (SQL Spackle) Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Jeff Moden
Posted Wednesday, January 19, 2011 9:37 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 32,906, Visits: 26,789
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."

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 #1050485
tommyh
Posted Wednesday, January 19, 2011 11:58 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:15 AM
Points: 1,476, Visits: 1,943
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
Post #1050527
David McKinney
Posted Thursday, January 20, 2011 12:59 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:43 AM
Points: 582, Visits: 1,601
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?
Post #1050551
David McKinney
Posted Thursday, January 20, 2011 1:08 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:43 AM
Points: 582, Visits: 1,601
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.
Post #1050552
Jeff Moden
Posted Thursday, January 20, 2011 1:14 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 32,906, Visits: 26,789
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."

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 #1050553
Jeff Moden
Posted Thursday, January 20, 2011 1:17 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 32,906, Visits: 26,789
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."

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 #1050554
marcin.motyl
Posted Thursday, January 20, 2011 1:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 27, 2011 7:22 AM
Points: 17, Visits: 13
Different method [I did not check for all languages]

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.
Post #1050559
surreydude.
Posted Thursday, January 20, 2011 2:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 19, 2013 4:50 AM
Points: 109, Visits: 114
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!
Post #1050576
katesl
Posted Thursday, January 20, 2011 2:53 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, April 27, 2013 5:57 PM
Points: 60, Visits: 405
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.."
Post #1050578
Koen Verbeeck
Posted Thursday, January 20, 2011 3:09 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 9,370, Visits: 6,468
Whupty-freakin'-doo! This is an amazing article!
I have never heard of DATENAME before, so thanks for pointing it out Jeff.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1050588
« Prev Topic | Next Topic »

Add to briefcase
92 posts, Page 1 of 10
12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.