Sql server tutorials

Updated : Oct 22, 2019 in Articles

Convert DateTime to regional languages in SQL

In this article, we learn how to convert DateTime to regional languages in SQL

The stored procedure, it will support up to 36 languages you will pass the Regional language values(0-9) to a procedure

Supported Languages:

select *from sys.syslanguages

English, German, French, Japanese, Danish, Spanish, Italian, Dutch, Norwegian, Portuguese, Finnish, Swedish, Czech, Hungarian, Polish, Romanian, Croatian, Slovak, Slovenian, ..etc

you can set the regional language values statically from 0 to 9 and pass the DateTime as a parameter to the [SP_RegionalDateConversion] store procedure will convert specify type language, execute the given below store procedure in your SQL server

In this example [SP_RegionalDateConversion] store procedure will convert DateTime to the Arabic language

Step 1 for convert DateTime to regional languages in SQL
CREATE Proc [dbo].[SP_RegionalDateConversion]
(
	@DateConValue date
)
as
 Begin
	 select @DateConValue as DateValue,convert(nvarchar(10), @DateConValue,103) as StringValue
     ,replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
        convert(nvarchar(10), @DateConValue,103)
        ,'0',N'٠')
        ,'1',N'١')
        ,'2',N'٢')
        ,'3',N'٣')
        ,'4',N'٤')
        ,'5',N'٥')
        ,'6',N'٦')
        ,'7',N'٧')
        ,'8',N'٨')
        ,'9',N'٩') as ArabicValue
 End

  
--exec [SP_RegionalDateConversion]
[email protected]='2022-10-22 20:24:34.887'
Convert DateTime regional languages in SQL

Step2: set language

which language you want to convert to DateTime to regional language first set language name( Japanese, Danish, Spanish …etc)

set language  Arabic
select DATENAME(dw, getdate()) + ',' + SPACE(1) + DATENAME(m, getdate()) + SPACE(1) + CAST(DAY(getdate()) AS VARCHAR(2)) + ',' + SPACE(1) + CAST(YEAR(getdate()) AS CHAR(4)) 
DateTime regional languages in SQL

Step 3: pass language as a parameter

DECLARE @DateTimeFormat AS DATETIME

SET @DateTimeFormat=GETDATE()

SELECT @DateTimeFormat AS [Date],FORMAT(@DateTimeFormat,'dddd/MMMM/yyyy','ar')

AS [arabic date]
DateTime conversion to regional languages

Finally one for converting DateTime to regional languages in SQL [FormatDateTimetoRegionalLang] function

CREATE FUNCTION [dbo].[FormatDateTimetoRegionalLang] 
( 
    @dt DATETIME, 
    @format VARCHAR(50),
    @lang VARCHAR(100)
) 
RETURNS VARCHAR(100) 
AS 
BEGIN 

DECLARE @Value VARCHAR(100)
DECLARE @ValueReplacement VARCHAR(100)
DECLARE @Ret VARCHAR(100)

IF NOT EXISTS(SELECT name
               FROM sys.syslanguages
                WHERE alias = @lang)
    RETURN dbo.FormatDateTime(​@dt​, @format)

SET @Ret = dbo.FormatDateTime(​@dt​, @format)

DECLARE curMonths CURSOR FOR
        SELECT Value = b.item, ValueReplacement = t.item
         FROM dbo.FN_Split((SELECT months FROM sys.syslanguages WHERE langid = @@langid),',') b
              INNER JOIN dbo.FN_Split((SELECT months FROM sys.syslanguages WHERE alias = @lang),',') t on b.row = t.row
OPEN curMonths
FETCH NEXT FROM curMonths INTO @Value, @ValueReplacement
WHILE @@FETCH_STATUS <> -1
BEGIN
    SET @Ret = REPLACE(@Ret, @Value, @ValueReplacement)
    FETCH NEXT FROM curMonths INTO @Value, @ValueReplacement
END
CLOSE curMonths
DEALLOCATE curMonths

DECLARE curShortMonths CURSOR FOR
        SELECT Value = b.item, ValueReplacement = t.item
         FROM dbo.FN_Split((SELECT shortmonths FROM sys.syslanguages WHERE langid = @@langid),',') b
              INNER JOIN dbo.FN_Split((SELECT shortmonths FROM sys.syslanguages WHERE alias = @lang),',') t on b.row = t.row
OPEN curShortMonths
FETCH NEXT FROM curShortMonths INTO @Value, @ValueReplacement
WHILE @@FETCH_STATUS <> -1
BEGIN
    SET @Ret = REPLACE(@Ret, @Value, @ValueReplacement)
    FETCH NEXT FROM curShortMonths INTO @Value, @ValueReplacement
END
CLOSE curShortMonths
DEALLOCATE curShortMonths

DECLARE curDays CURSOR FOR
        SELECT Value = b.item, ValueReplacement = t.item
         FROM dbo.FN_Split((SELECT days FROM sys.syslanguages WHERE langid = @@langid),',') b
              INNER JOIN dbo.FN_Split((SELECT days FROM sys.syslanguages WHERE alias = @lang),',') t on b.row = t.row
OPEN curDays
FETCH NEXT FROM curDays INTO @Value, @ValueReplacement
WHILE @@FETCH_STATUS <> -1
BEGIN
    SET @Ret = REPLACE(@Ret, @Value, @ValueReplacement)
    FETCH NEXT FROM curDays INTO @Value, @ValueReplacement
END
CLOSE curDays
DEALLOCATE curDays

RETURN @Ret

END 

Latest posts by DuttaluruVijayakumar (see all)
Like
Like Love Haha Wow Sad Angry

Subscribe
Notify of
guest
1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Carey Berumen
Carey Berumen
March 20, 2020 8:56 am

I really appreciate your help with my project!

1
0
Would love your thoughts, please comment.x
()
x