1

• How can I list the supported regional ISO codes per the version I work on?

I need to check an ISO 639-1 (2 characters) language code to see if it is supported. You don't get a default reply for versions prior to 2019. It throws an error.

Example for the requirement:

select format(current_timestamp,'dd-MMMM-yyyy','es')
select format(current_timestamp,'dd-MMMM-yyyy','mn')
select format(current_timestamp,'dd-MMMM-yyyy','ii')
select format(current_timestamp,'dd-MMMM-yyyy','oo')
select format(current_timestamp,'dd-MMMM-yyyy','qq')

The above will return 2 errors on (my) v.2016 – The culture parameter 'oo' provided in the function call is not supported. The culture parameter 'qq' provided in the function call is not supported.

This doesn't match the definitions listed in sys.syslanguages And that table does not include the iso code either.

Thank you!

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
Barak G.
  • 224
  • 3
  • 6
  • See [Formatting types](https://learn.microsoft.com/en-us/dotnet/standard/base-types/formatting-types) for the documentation – Thom A Dec 24 '21 at 17:45
  • I don't see an error running `format(current_timestamp,'dd-MMMM-yyyy','qq')` on my local 2016 instance. I suspect it will be OS dependant. `CultureInfo.CreateSpecificCulture("qq")` ends up creating a Culture with DisplayName "Invariant Language (Invariant Country)" and `TwoLetterISOLanguageName` of `iv` in a .net console app for me on my dev machine. – Martin Smith Dec 24 '21 at 17:54
  • 1
    .NET tag added because the SQL Server `format` function is implemented by calling out to CLR (and I imagine it is basically the same Q as "under what circumstances does `DateTime.Now.ToString("dd-MMMM-yyyy", new CultureInfo("qq"))` work)" – Martin Smith Dec 24 '21 at 18:11
  • 2
    The answer will be "not easily", unless you're really willing to write and deploy a CLR function, which seems like overkill for this specific scenario. The information on what language codes are accepted by the runtime is simply not reflected in SQL Server itself. If you truly have a need for this (with variable cultures), it suggests you shouldn't be doing it in SQL Server to begin with -- which, really, is true for formatting values for end-user consumption in general. This sort of thing is much easier managed client-side. – Jeroen Mostert Dec 24 '21 at 21:51
  • There still should be a call from the function to a repository of languages one could query. I deduced the version is resolving the door, but your comment change that perspective. – Barak G. Dec 25 '21 at 18:07
  • .NET is not a good solution for me. I have scripted a call to the function within a try catch block that create a list I can save. It's just that wanted to learn where the ISO codes are stored. – Barak G. Dec 25 '21 at 18:09
  • The problem is that the version involved is the version of the runtime, not the SQL Server version (and, from .NET 4 onwards, also the version of the OS, although I don't know if this affects the cultures available for date formatting). Technically it's true that the language codes are stored somewhere, but they're not stored in the form of a system table or view, so they can't be queried. SQL Server is literally "blind" here since it just passes through the request. With a .NET function you can do things like `CultureInfo.GetCultures(CultureTypes.AllCultures).` – Jeroen Mostert Dec 26 '21 at 09:59
  • @jeroen calling that still doesn't return a culture with two letters qq for me even though somehow it doesn't return an error and is treated as invariant – Martin Smith Dec 27 '21 at 12:56
  • 1
    @MartinSmith: well, there's always the source, and decompilers... I didn't mean to imply `CultureInfo.GetCultures` was going to give "the" list of values accepted, but there is "a" list of such behavior that can (at least in theory) be extracted using .NET in a way that is not possible even in theory from SQL Server alone (except through tediously trying all possible combinations, as is done in the current answer). Note that the question of whether `qq` is accepted in some shape or fashion *ought* to be academic, because `qq` is unassigned, so we're really after defining undefined behavior. – Jeroen Mostert Dec 27 '21 at 13:49

1 Answers1

0

Per martin's answer, this differs from one environment to another. The ugly way will work, yet a list from the internal tables to work on would be nice.

drop table if exists #SupportedLanguages;
create table #SupportedLanguages(Lang varchar(2));
declare c cursor fast_forward local for
select concat(a.value,b.value)
from string_split('a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z',',')a
    cross apply string_split('a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z',',')b
open c;

declare @c varchar(2), @cmd varchar(100);
fetch next from c into @c;

while @@FETCH_STATUS = 0
begin
    select @cmd = concat('select format(current_timestamp,''dd-MMMM-yyyy'',''', @c, ''')')
    begin try
        exec(@cmd);
        insert into #SupportedLanguages values(@c);
    end try
    begin catch
    end catch
    fetch next from c into @c;
end;

close c;
deallocate c;
Barak G.
  • 224
  • 3
  • 6