sql - 为什么我的 'WHERE' 子句基于 'DATE' 失败？

Question

我正在使用 node.js 连接到 SQL 数据库(特别是 SQL Server 2016)。我的表名为 transactionCounts，具有以下表和数据类型:

staff_id:varchar(50)，日期:日期，计数:int；

为了清楚起见，“日期”字段只是一个日期，而不是日期时间。记录如下所示:“2017-08-07”

我正在使用 mssql 包，const sql = require('mssql');

基本上，我有一个函数接受开始日期和结束日期，并用它们来执行此操作:

function(start, end) {

    let ps = new sql.PreparedStatement(transactionRegisterSqlPool);

    ps.input('start', sql.Date);
    ps.input('end', sql.Date);

    ps.prepare('SELECT staff_id, SUM(Count) TotalCount FROM [TransactionRegister].[dbo].[transactionCounts] ' +
        'WHERE date >= @start AND date < @end GROUP BY staff_id', err => {
            .execute({start: start, end: end}, (err, result) => {});
        });
};

为了便于说明，我简化了该函数(它通常返回一个 promise )，但这里出了问题:

我传入了 8 月 20 日午夜和 8 月 27 日午夜的日期，我期望返回的是日期 20,21,22,23,24,25 和 26 的总和(自然是 7 天) ，一周)。

26 号虽然(肯定)没有被包括在内，我也不完全确定，但我打赌 19 号会被包括在内。我认为这是一个夏令时问题，因为当我调用 .toISOString() 时，这些日期看起来像 2017-08-19T23:00:00.000Z 和 >2017-08-26T23:00:00.000Z 分别(前一天晚上 11 点)。

我已经修改了我的函数以使用字符串而不是日期，这似乎有效并返回正确的总和:

function(start, end) {

    let ps = new sql.PreparedStatement(transactionRegisterSqlPool);

    ps.input('start', sql.VarChar);
    ps.input('end', sql.VarChar);

    start = `${start.getFullYear()}/${start.getMonth() + 1}/${start.getDate()}`;
    end = `${end.getFullYear()}/${end.getMonth() + 1}/${end.getDate()}`;

    ps.prepare('SELECT staff_id, SUM(Count) TotalCount FROM [TransactionRegister].[dbo].[transactionCounts] ' +
        'WHERE date >= @start AND date < @end GROUP BY staff_id', err => {
            ps.execute({start: start, end: end}, (err, result) => {});
        });
};

但是，将我的日期转换为字符串来解决这个问题似乎是错误的。处理 Javascript 日期和 SQL 日期之间的日期的正确方法是什么，以避免这种明显的夏令时引起的问题？

Answer 1

你的问题是JavaScript没有“日期”类型，只有“日期时间”，但SQL有“日期”类型。因此，您必须进行转换。

如果将其包装在函数中，它仍然可读:

function toDateString(d) {
   return `${d.getFullYear()}/${d.getMonth() + 1}/${d.getDate()}`; 
}

ps.prepare('SELECT staff_id, SUM(Count) TotalCount FROM [TransactionRegister].[dbo].[transactionCounts] ' +
    'WHERE date >= @start AND date < @end GROUP BY staff_id', err => {
        ps.execute({start: toDateString(start), end: toDateString(end)}, (err, result) => {});
    });