Formatting Dates with 3 Character Months (SQL Spackle)

Question

Formatting Dates with 3 Character Months (SQL Spackle)

Viewing 4 posts - 106 through 108 (of 108 total)

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

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

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.

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)

Miles Neale SSChampion Points: 13147 More actions · Answer 2

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!

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 3

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

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'

*/

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 4

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.