June 24, 2022 at 5:05 pm
I need to update a report to show the month name in the specific language - creole.
I use the function below which uses sys.syslanguages table.
I need the month names for creole now but this table does not have Creole.
Is it safe to update this system table and Creole?
Any ideas?
Thank you.
ALTER FUNCTION pec.[fn_GetMonthName] (
@Date DATETIME,
@Language NVARCHAR(100)
)
RETURNS NVARCHAR(400)
AS
/*
Sample:
SELECT pec.fn_GetMonthName (GETDATE(), 'Spanish') + ' ' + CAST(DAY(GETDATE()) AS CHAR(2)) + ', ' + CAST(YEAR(GETDATE()) AS CHAR(4)) AS DateInSpanish
*/
BEGIN
DECLARE @i INT, @m INT,@mlist NVARCHAR(1000)
SET @m = MONTH(@Date)
SET @mlist = (SELECT months FROM sys.syslanguages WHERE ALIAS = @language)
SET @i = 1
WHILE(@i < @m)
BEGIN
SET @mlist = REPLACE(@mlist, SUBSTRING(@mlist,1,CHARINDEX(',',@mlist)) ,'')
SET @i = @i + 1
END
SET @mlist = (CASE CHARINDEX(',',@mlist) WHEN 0 THEN @mlist ELSE SUBSTRING(@mlist,0,CHARINDEX(',',@mlist) ) END )
RETURN @mlist
END
GO
June 24, 2022 at 6:18 pm
No, don't mess w/ the system table.
Just use your own table, function, or hard-coded case statement for Creole -- or other unsupported languages (don't use a comma-delimited list -- use a separate row for each month to avoid the loop) when language is Creole -- e.g.,
CREATE TABLE dbo.CreoleMonths
(
MonthNumber INT NOT NULL PRIMARY KEY,
MonthName VARCHAR(100) NOT NULL
);
if language = 'Creole'
begin
SET @mlist = (SELECT MonthName FROM dbo.CreoleMonths WHERE MonthNumber = @m);
end
else
begin
-- current logic
end
June 24, 2022 at 6:25 pm
thanks sir - kinda figured that !
June 24, 2022 at 7:19 pm
thanks sir - kinda figured that !
Heh.. I wouldn't use the "current logic"... it uses some pretty slow RBAR. Use DelimitedSplitN4K, instead.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 24, 2022 at 7:28 pm
lol..yeah I've heard bad things about current logic
googles ' DelimitedSplitN4K'....oh cool. Thanks Jeff!
June 24, 2022 at 7:33 pm
Use DelimitedSplitN4K, instead
I started to suggest STRING_SPLIT until I noticed 2014. Definitely DelimitedSplitN4K over that loop.
krypto69: Is that function call only used once for a title, or are you using it in rows? If multiple rows, you should also consider refactoring it into an ITVF (inline table-valued function).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply