Search code examples
sql-serverwindows-server-2012-r2sql-server-2017sql-date-functions

SQL Server FORMAT with culture parameter not working

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.

Solution

  • 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