Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««7891011

Formatting Dates with 3 Character Months (SQL Spackle) Expand / Collapse
Author
Message
Posted Friday, July 19, 2013 7:36 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 35,348, Visits: 31,886
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1475483
Posted Friday, July 19, 2013 8:24 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, October 17, 2014 9:48 AM
Points: 106, Visits: 510
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.
Post #1475513
Posted Friday, July 19, 2013 8:32 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, October 17, 2014 9:48 AM
Points: 106, Visits: 510
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.
Post #1475519
Posted Friday, July 19, 2013 9:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:23 PM
Points: 2,395, Visits: 1,478
I had not seen this previously when it was posted on the site. It is great -> Thanks!

Not all gray hairs are Dinosaurs!
Post #1475561
Posted Friday, July 19, 2013 11:50 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 35,348, Visits: 31,886
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1475619
Posted Friday, July 19, 2013 11:53 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 35,348, Visits: 31,886
Miles Neale (7/19/2013)
I had not seen this previously when it was posted on the site. It is great -> Thanks!


Thanks for the feedback, Miles. I appreciate it. Heh... I appreciate your signature line, as well. I have gray hair in places where some people don't have places 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1475622
Posted Friday, July 19, 2013 12:04 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:23 PM
Points: 2,395, Visits: 1,478
Jeff

I have been in IT most of past 42 years and am glad to still have some hair even though it is now gray/white. I was thinking this morning that IBM use to have that old "THINK" sign as a slogan. Contemplating that, I believe that what keeps us interested and "young" is that we continue learning. Your efforts to publish new and interesting things keeps us learning and it is appreciated.



Not all gray hairs are Dinosaurs!
Post #1475633
Posted Friday, July 19, 2013 4:30 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
Jeff, I'm glad this discussion was resurrected since I missed it the first time around. I too was not aware of the sys.syslanguages table! There have been a few occasions when I wanted to use SET LANGUAGE or SET DATEFORMAT in functions...but SQL won't let us do that.

The procedure below doesn't really address that directly, but while studying the syslanguages table I decided to write a short procedure that will set the date format based on the language ID. I figured I'd just toss it out and perhaps it can be useful to someone dealing with international dates.

If a date is entered, it will be returned in the designated language format. If no date is entered, the procedure will output the codes from the table so that SET DATEFORMAT and SET LANGUAGE can be instantiated outside of the procedure. An example script is below.


CREATE PROCEDURE dbo.UTIL_SetLanguageParams

@pLangID INT
,@pFormat INT
,@pStrDate NVARCHAR(30)

AS
BEGIN

SET NOCOUNT ON

DECLARE
@dateformat NCHAR(3)
,@name NVARCHAR(20)
,@alias NVARCHAR(20)
,@pDate DATETIME

SET DATEFORMAT YMD

IF @pStrDate IS NOT NULL
AND ISDATE(CAST(@pStrDate AS DATETIME)) = 1
SET @pDate = @pStrDate

IF @pLangID IS NULL OR @pLangID > 32
SET @pLangID = 0


IF @pFormat IS NULL
SET @pFormat = 101

SELECT
@dateformat = dateformat
,@name = name
,@alias = alias
FROM
sys.syslanguages AS s
WHERE
langid = @pLangID

SET LANGUAGE @name
SET DATEFORMAT @dateformat


DECLARE
@SQLString NVARCHAR(MAX)
,@ParmDefinition NVARCHAR(4000)

SET @SQLString = N'SELECT @result = CONVERT(NVARCHAR(20),'''+CAST(@pDate AS NVARCHAR(20))+''','+CAST(@pFormat AS NVARCHAR(5))+')'
SET @ParmDefinition = N'@result NVARCHAR(4000) OUTPUT'
EXECUTE sp_executesql @SQLString, @ParmDefinition, @result = @pDate OUTPUT;

IF @pDate IS NOT NULL
SELECT CONVERT(NVARCHAR(20),@pDate,@pFormat) AS FormattedDate
ELSE
BEGIN
SELECT
@name AS LName
,@dateformat AS DFormat
END

END
GO



IF OBJECT_ID('tempdb..#LangSettings') IS NOT NULL
DROP TABLE #LangSettings

CREATE TABLE #LangSettings (
[ID] INT IDENTITY(1,1) NOT NULL,
[LName] NVARCHAR(50) NULL,
[DFormat] NVARCHAR(50) NULL,
PRIMARY KEY (ID))

INSERT INTO #LangSettings
EXEC dbo.UTIL_SetLanguageParams 22,113,NULL

DECLARE
@LName NVARCHAR(50)
,@DFormat NVARCHAR(50)

SELECT
@LName = LName
,@DFormat = DFormat
FROM
#LangSettings

SET LANGUAGE @LName
SET DATEFORMAT @DFormat
SELECT CONVERT(NVARCHAR(20),GETDATE(),113)


Sample usage:


/*
EXEC dbo.UTIL_SetLanguageParams 0,113,NULL
EXEC dbo.UTIL_SetLanguageParams 2,113,NULL
EXEC dbo.UTIL_SetLanguageParams 0,113,'2013-07-19'
EXEC dbo.UTIL_SetLanguageParams 2,113,'2013-07-19'
EXEC dbo.UTIL_SetLanguageParams 12,113,'2013-07-19'
EXEC dbo.UTIL_SetLanguageParams 22,113,'2013-07-19'
EXEC dbo.UTIL_SetLanguageParams 30,113,'2013-07-19'
*/


Post #1475710
Posted Friday, July 19, 2013 5:07 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 758, Visits: 1,031
I think format() is part of the .Net stack, and it wouldn't surprise me if there is an overhead to calling out to .Net functions rather than using native SQL Server functions. However, with the built-in support for locales, it's still useful.

I will say in its favour is that it's identical syntax to formatting in reporting services, and I wouldn't be surprised if there's something the same in SSIS (bit rusty there). Kind of a blah benefit though.

I tend to use cast() where I can, then convert(), because cast() is portable across Oracle and SQL Server (the two platforms I work on) and they're also portable across versions of SQL Server. So format() is kind of cool, but the only time I've used it thus far I had to turn around and pull it back out so I could get the code running on SQL Server 2008.

Another related aside: using the date functions (like datename, datepart, dateadd, datediff, etc) resolves so many logic issues when converting between implicit date formats that it's just good practice to use them, irrespective of speed. This happens all the time in NZ -- the default installation date format is the us MM/DD/YYYY, but New Zealand date format is DD/MM/YYYY, and we often have code moving between the two locales.
Post #1475714
« Prev Topic | Next Topic »

Add to briefcase «««7891011

Permissions Expand / Collapse