Reference for check correct culture in sql server for Format function

Question

Clients use custom culture to view the website for themselves.

This culture setting is saved per user in the database, and this setting is accessed using the format field in the select query as follows:

DECLARE @TimeZone VARCHAR(31) = (SELECT name FROM sys.time_zone_info AS TZI WHERE TZI.current_utc_offset = '+03:30')
DECLARE @Ctype VARCHAR(5) = 'fa' -- 'de-de' or 'en-US' or 'zh-cn'

SELECT 
Format( CONVERT(datetime,U.CreatedDate AT TIME ZONE 'UTC' AT TIME ZONE  @TimeZone  ) ,'yyyy/MM/dd | hh:mm',@Ctype)
FROM UserSetting AS U

How to find @Ctype with a correct refrence. I use sys.time_zone_info system table for saving valid time zones and checking user changes.

For checking user data entry validation, I need a reference for culture type. We need a dynamic reference so that all valiad cultures are applied to SQL Server because some users can set not valiad cultures.

I can't find useful document in microsoft about Format culture.

I need a sys table or other reference for find valiad culture in sql server 2019, our base language for develop is tSQL. for set table as follows:

Declare @UserId int = 1
Declare @TimeZone sysname, @CultureType sysname

-- validation
IF NOT EXISTS (SELECT 1 FROM sys.time_zone_info AS TZI WHERE TZI.name = @TimeZone )
  Return 'False time zone'
IF (@Culture NOT IN ('fa','en','ar','en-us')) -- THIS IS MY PROBLEM, I need check all true cultures
  Return 'False culture'


INSERT INTO UserStting (UserId,TimeZone,Culture)
Values (@UserId,@TimeZone,@CultureType)

This article is helpful for finding all cultures in sql-server-helper, but I need a better way, whitout hard code, like timezone table.

Answer 1

If you did not find any other solution, you can validate using below procedure.

CREATE PROCEDURE dbo.ValidateCulture
    @culture sysname, @is_valid BIT = NULL OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET @is_valid = ISNULL(@is_valid, 0)
        
    BEGIN TRY
        SELECT Format( CONVERT(datetime, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE  'Iran Standard Time'  ) ,'yyyy/MM/dd | hh:mm', @culture)        
        SET @is_valid = 1;
    END TRY
    BEGIN CATCH
        IF (ERROR_NUMBER() <> 9818)
            THROW;
    END CATCH
END