Chinese environment, dataname function return value for the English environment value

[yixiu025]

Hi, When I used mssql's dataname(), I got a unexcepted return value. My environment is Chinese, and I want the range to be two digits per month. I suspected that I had missed some configuration, but after looking through the documentation I didn't find any configuration parameters related to the language

it's my sql: sql:

select datename(MONTH, getdate());

Expected behaviour:

05

Actual behaviour:

May

Configuration:

const config = {
  user: "[uname]",
  password: "[pwd]",
  server: "[ip]",
  database: "[dbname]",
  port: 1433,
  connectionTimeout: 3000000,
  requestTimeout: 3000000,
  options: {
    encrypt: false,
    useUTC: false
  },
  pool: {
    min: 0,
    max: 10,
    idleTimeoutMillis: 3000000,
  },
};

Software versions

• NodeJS: 14.21.0
• node-mssql: 9.1.1
• SQL Server: Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64) Apr 29 2016 23:23:58 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows 10 Home China 6.3 (Build 22621: ) (Hypervisor)

[dhensby]

DATENAME appears to be working as documented. Your expected behaviour is not correct and the actual behaviour is correct.

You may want to try DATEPART, which will return a numeric value for month.

[yixiu025]

Thank you for your reply, I tested again and found the following phenomenon:

When I connect to sqlserver using connect string and a parameter named Language, datename() returns the correct result.

Here is my code:

const mssql = require("mssql");
const db = {};
const config = {
  user: "u",
  password: "p",
  server: "127.0.0.1",
  database: "demo",
  port: 1433,
  connectionTimeout: 3000000,
  requestTimeout: 3000000,
  options: {
    encrypt: false,
    useUTC: false
  },
  pool: {
    min: 0,
    max: 10,
    idleTimeoutMillis: 3000000,
  },
};

db.query = (sql, params) => {
  return new Promise(async (resolve, reject) => {
    try {
      // using config
      // await mssql.connect(config);

      // using connect string
      // set language to en_us
      // await mssql.connect(`Server=127.0.0.1,1433;Database=demo;User Id=u;Password=p;Encrypt=false;Language=us_english;Min Pool Size=0;Max Pool Size=10;`);
      // set language to zh_cn
      await mssql.connect(`Server=127.0.0.1,1433;Database=demo;User Id=u;Password=p;Encrypt=false;Language=简体中文;Min Pool Size=0;Max Pool Size=10;`);
      const request = new mssql.Request();

      resolve(await request.query(sql));
    } catch (err) {
      reject(err);
    }
  });
};

db.query("select top 1 @@LANGUAGE from dt").then(res => {
  console.log(res);
});
db.query("select top 1 datename(MONTH, getdate()) from dt").then(res => {
  console.log(res);
});

This is the return result of using the config:

{
  recordsets: [ [ [Object] ] ],
  recordset: [ { '': 'us_english' } ],
  output: {},
  rowsAffected: [ 1 ]
}
{
  recordsets: [ [ [Object] ] ],
  recordset: [ { '': 'June' } ],
  output: {},
  rowsAffected: [ 1 ]
}

Use the connect string and set the Language parameter to zh_cn:

{
  recordsets: [ [ [Object] ] ],
  recordset: [ { '': '06' } ],
  output: {},
  rowsAffected: [ 1 ]
}
{
  recordsets: [ [ [Object] ] ],
  recordset: [ { '': '简体中文' } ],
  output: {},
  rowsAffected: [ 1 ]
}

Also, when I set the Language parameter of the connection string to a different value, I tracked different results in the SQLServer Profileer tool. When using en_us:

-- network protocol: TCP/IP
set quoted_identifier off
set arithabort off
set numeric_roundabort off
set ansi_warnings off
set ansi_padding off
set ansi_nulls off
set concat_null_yields_null off
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

When using zh_ch:

-- network protocol: TCP/IP
set quoted_identifier off
set arithabort off
set numeric_roundabort off
set ansi_warnings off
set ansi_padding off
set ansi_nulls off
set concat_null_yields_null off
set cursor_close_on_commit off
set implicit_transactions off
set language 简体中文
set dateformat ymd
set datefirst 7
set transaction isolation level read committed

The Language parameter is the one I found in the @tediousjs package.

Can this parameter also be added to the config to set the language to be used when connecting?

[dhensby]

The reason the language isn't being set when using the config object you supply is because it's not setting a language, but the connection string does.

Can this parameter also be added to the config to set the language to be used when connecting?

Yes, this is what the connection string does. You'd need to add it to the config a bit like so (I think, not tested):

const mssql = require("mssql");
const db = {};
const config = {
  user: "u",
  password: "p",
  server: "127.0.0.1",
  database: "demo",
  port: 1433,
  connectionTimeout: 3000000,
  requestTimeout: 3000000,
  options: {
+  language: 'english',
    encrypt: false,
    useUTC: false
  },
  pool: {
    min: 0,
    max: 10,
    idleTimeoutMillis: 3000000,
  },
};

[yixiu025]

It worked, thanks.

I have discovered this phenomenon: When I set the language parameter to "" or an invalid value, it takes the default language configured in the database When I set language name or language alias, it uses the language I set

Why doesn't it use the default language of the database when I don't set the language?

[dhensby]

That may be worth raising with the tedious library as a potential bug.

[yixiu025]

I got it. Thank you.