如何编写仅展示当日数据的SQL查询实现谷歌表格脚本自动化

修复要点 #

1. 原SQL末尾AND OrderStatus.OrderStatusName <> 'Cancelled;存在单引号未闭合的语法错误，修改时需要补上末尾的单引号。
2. 将WHERE子句中硬编码的固定日期区间，替换为数据库内置的当日日期计算逻辑，无需手动修改日期。

不同数据库的SQL修改示例 #

SQL Server（适配你之前尝试的GETDATE()函数） #

你之前使用GETDATE()未生效大概率是未截断函数返回的时间部分，调整写法如下：

Left Join
OrderType ON SalesOrder.OrderTypeID = OrderType.OrderTypeID
Left Join
Location On EventRef.LocationID = Location.LocationID
Inner Join
Client On Location.ClientID = Client.ClientID
Inner Join
RequestSource On SalesOrder.RequestSourceID = RequestSource.RequestSourceID

WHERE 
EventRef.EventDateTime >= CAST(GETDATE() AS DATE)
AND EventRef.EventDateTime < DATEADD(DAY, 1, CAST(GETDATE() AS DATE))
AND OrderStatus.OrderStatusName <> 'Added in error'
AND OrderStatus.OrderStatusName <> 'Cancelled'

注：采用「大于等于当日0点、小于次日0点」的判断逻辑，比写死到23:59:59兼容性更强，不会遗漏毫秒级时间戳的记录

MySQL #

Left Join
OrderType ON SalesOrder.OrderTypeID = OrderType.OrderTypeID
Left Join
Location On EventRef.LocationID = Location.LocationID
Inner Join
Client On Location.ClientID = Client.ClientID
Inner Join
RequestSource On SalesOrder.RequestSourceID = RequestSource.RequestSourceID

WHERE 
DATE(EventRef.EventDateTime) = CURDATE()
AND OrderStatus.OrderStatusName <> 'Added in error'
AND OrderStatus.OrderStatusName <> 'Cancelled'

Oracle #

Left Join
OrderType ON SalesOrder.OrderTypeID = OrderType.OrderTypeID
Left Join
Location On EventRef.LocationID = Location.LocationID
Inner Join
Client On Location.ClientID = Client.ClientID
Inner Join
RequestSource On SalesOrder.RequestSourceID = RequestSource.RequestSourceID

WHERE 
TRUNC(EventRef.EventDateTime) = TRUNC(SYSDATE)
AND OrderStatus.OrderStatusName <> 'Added in error'
AND OrderStatus.OrderStatusName <> 'Cancelled'

谷歌表格脚本适配方案 #

如果你更倾向于在谷歌表格脚本层面处理日期拼接，可以直接在JS中生成对应格式的当日日期，再拼接入SQL语句，参考代码：

// 生成和原格式对齐的日期字符串，示例输出：11-Oct-21
function getTodayFormatStr() {
  const today = new Date();
  const monthAbbr = ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'][today.getMonth()];
  const date = today.getDate();
  const yearSuffix = String(today.getFullYear()).slice(-2);
  return `${date}-${monthAbbr}-${yearSuffix}`;
}

// 拼接SQL
const todayStr = getTodayFormatStr();
const finalSql = `Left Join
OrderType ON SalesOrder.OrderTypeID = OrderType.OrderTypeID
Left Join
Location On EventRef.LocationID = Location.LocationID
Inner Join
Client On Location.ClientID = Client.ClientID
Inner Join
RequestSource On SalesOrder.RequestSourceID = RequestSource.RequestSourceID

WHERE 
EventRef.EventDateTime > '${todayStr} 0:00:00 AM' 
AND EventRef.EventDateTime < '${todayStr} 23:59:59 PM' 
AND OrderStatus.OrderStatusName <> 'Added in error'
AND OrderStatus.OrderStatusName <> 'Cancelled'`;

内容的提问来源于stack exchange，提问作者DavidBill7