Azure Cosmos DB中查询指定日期区间内数据行数的SQL语句实现

嗨，这个问题我熟！咱们来一步步搞定它～

首先，你的Azure Cosmos DB里日期是用"YYYY-MM-DD"字符串存的，要统计任意格式的两个日期之间的数据，核心是把存储的日期和输入的日期统一转换成可比较的类型，不然直接字符串对比很容易出问题（比如输入是"11/18/2020"这种格式，字符串比出来的结果肯定不对）。

给你两种实用方案，按需选：

方案一：类型转换后比较（通用型） #

利用Cosmos DB的DateTimeFromString()函数，把存储的字符串日期和输入的两个日期都转换成DateTime类型，再用BETWEEN做范围判断。这样不管输入的date1/date2是什么格式（只要能被该函数正确解析），都能正常统计。

示例查询语句（推荐用参数化查询，避免注入问题）：

SELECT VALUE COUNT(1) 
FROM table 
WHERE DateTimeFromString(table.date) BETWEEN DateTimeFromString(@date1) AND DateTimeFromString(@date2)

贴心补充： #

如果担心用户选的date1比date2大（比如不小心把结束日期选成了开始日期），可以用LEAST()和GREATEST()函数自动调整顺序，避免统计结果为0：

SELECT VALUE COUNT(1) 
FROM table 
WHERE DateTimeFromString(table.date) BETWEEN 
    LEAST(DateTimeFromString(@date1), DateTimeFromString(@date2)) AND 
    GREATEST(DateTimeFromString(@date1), DateTimeFromString(@date2))

方案二：统一字符串格式后比较（性能更优） #

如果你的存储格式是固定的"YYYY-MM-DD"，那可以在应用层先把用户输入的任意格式日期转换成"YYYY-MM-DD"字符串，然后直接用字符串做范围对比。这种方式不需要转换每个文档的日期字段，查询性能会更好。

示例查询语句：

SELECT VALUE COUNT(1) 
FROM table 
WHERE table.date BETWEEN @formattedDate1 AND @formattedDate2

（这里的@formattedDate1和@formattedDate2是你在应用层转好的"YYYY-MM-DD"格式字符串）

注意事项 #

• 一定要用参数化查询（像上面的@date1、@formattedDate1这种），别直接拼接SQL字符串，既安全又能让Cosmos DB优化查询。
• 如果输入的日期格式特别小众（比如"DD-MM-YYYY"），DateTimeFromString()可能解析错误，这时候优先在应用层把日期转成ISO 8601格式（比如"2020-11-18"）再传入查询更稳妥。
• BETWEEN是包含边界值的，如果不需要包含date1或date2的记录，换成>和<就行。

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