3

I am using SQL Server 2017 and try to use culture in the Format function. When running this Query on the server the result is incorrect:

SELECT @@VERSION --> Microsoft SQL Server 2017 (RTM-CU17) (KB4515579) - 14.0.3238.1 (X64)   Sep 13 2019 15:49:57   Copyright (C) 2017 Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64
SELECT FORMAT(SYSDATETIME(), N'Y', 'fa'); --> February 2020

But when running this in another server with SQL server 2017 the script works fine:

SELECT @@VERSION --> Microsoft SQL Server 2017 (RTM-CU3-GDR) (KB4052987) - 14.0.3015.40 (X64)   Dec 22 2017 16:13:22   Copyright (C) 2017 Microsoft Corporation  Enterprise Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 16299: ) 
SELECT FORMAT(SYSDATETIME(), N'Y', 'fa'); --> بهمن, 1398

What has configuration must be checked?

P.S: I realized that this problem occurres on Windows Server 2012 R2 and lower versions.

piet.t
  • 11,718
  • 21
  • 43
  • 52
Fred
  • 3,365
  • 4
  • 36
  • 57
  • What do you get if you execute `[string]::Format([System.Globalization.CultureInfo]::GetCultureInfo("fa"), "{0:Y}", [DateTime]::Now)` in Powershell on the first server? – GSerg Feb 08 '20 at 08:31
  • @GSerg It is not working on the first server. and returns February 2020, but in the second server, it works fine. – Fred Feb 08 '20 at 09:23
  • 3
    Thus we have determined that there's something wrong with the .NET Framework on the first server, rather than with the SQL Server. – GSerg Feb 08 '20 at 10:23

2 Answers2

2

Finally, I found the problem. This feature depends on the version of Windows which SQL server installed on it. Persian calendar was added in Windows Server 2012 R2 And Windows 10.

This image shows the date format for fa culture in Windows Server 2012 R2 and Windows 10

New Date Format

And this image shows the date format for fa culture in the older version of Windows

Old Date Format

Fred
  • 3,365
  • 4
  • 36
  • 57
0

In fact, the problem is at Windows Server where the SQLServer is installed. For any language you want to use, Windows Server must have this language installed at OS level. To install in "Windows Server 2012 R2" or later, just follow these steps:

  • Search for "Control Panel" and open it
  • Select "View by: Large Icons"
  • Click on "Languages"

Add the Language you need, and after that also install the "Language Pack" for the new language added.

After this, your parameter in FORMAT function will be accepted.

FabioFernandes
  • 1
  • 1