Đếm đơn theo ngày: BETWEEN, mốc .999 và giờ UTC

1. Vấn đề: một đơn lúc nửa đêm bị đếm vào hai ngày #

Báo cáo doanh thu theo ngày của BanHang chạy qua Dapper với hai mốc 2026-10-01 00:00:00 và 2026-10-01 23:59:59.999, điều kiện WHERE NgayTao BETWEEN @Tu AND @Den. Ba đơn minh họa: đơn 1 lúc 01/10 00:00:00 trị giá 100.000, đơn 2 lúc 01/10 23:59:59 trị giá 200.000, đơn 3 lúc 02/10 00:00:00 trị giá 300.000.

Báo cáo ngày 01/10 ra 3 đơn, 600.000 đồng. Đơn 3 thuộc ngày 02/10, và báo cáo ngày 02/10 cũng đếm nó. Cộng hai ngày, doanh thu của đơn 3 bị tính hai lần.

Một API báo cáo khác nhận mốc từ ứng dụng di động dạng DateTimeOffset, 2026-10-01T00:00:00+07:00, và truyền thẳng xuống. Nó đã dùng khoảng nửa mở, nhưng chỉ đếm được 1 đơn, 100.000 đồng. Cột NgayTao lưu giờ Việt Nam, còn phép so coi nó là giờ UTC, nên khoảng bị lệch 7 giờ.

Cả hai lỗi không báo gì. Số sai đi thẳng vào báo cáo đối soát.

2. Mục đích: mỗi đơn rơi vào đúng một ngày, và lọc ngày vẫn seek #

• Ngày 01/10 ra đúng 2 đơn, 300.000 đồng, dù ứng dụng gửi mốc dạng datetime, datetime2 hay DateTimeOffset.
• Lọc một ngày trên bản thử 218.000 đơn đọc dưới 10 page, kể cả khi cột lưu UTC còn báo cáo tính theo ngày Việt Nam.
• NgayTao giờ Việt Nam được tính từ UTC, không phụ thuộc múi giờ của máy chạy API hay máy chủ database.

3. Cơ sở lý thuyết: kiểu nào làm tròn, vế nào bị đổi kiểu #

datetime làm tròn mốc cuối ngày lên ngày sau #

datetime không lưu từng mili giây. Phần lẻ giây được làm tròn về bội số gần nhất của 1/300 giây, hiện ra là .000, .003, .007. CAST('2026-10-01T23:59:59.998' AS datetime) ra 23:59:59.997. CAST('2026-10-01T23:59:59.999' AS datetime) ra 2026-10-02 00:00:00.000, tức đầu ngày hôm sau. datetime2(0) làm tròn phần lẻ giây theo cách thông thường, nên .997 vào datetime2(0) cũng thành 2026-10-02 00:00:00.

BETWEEN a AND b lấy cả hai đầu. Mốc cuối bị làm tròn lên đúng nửa đêm thì đơn tạo lúc nửa đêm hôm sau nằm trong khoảng. Mẫu BETWEEN ... '23:59:59.997' chỉ đúng khi cả cột lẫn tham số là datetime. Với cột datetime2(3) có đơn lúc 23:59:59.998 và .999, mốc đó bỏ sót cả hai.

Kiểu tham số do thư viện chọn #

Ứng dụng không gửi chuỗi mà gửi tham số, và kiểu của tham số do thư viện chọn. Dapper và SqlCommand.Parameters.AddWithValue gửi DateTime của .NET dạng datetime, nên mốc .999 đã thành nửa đêm hôm sau trước khi tới câu lệnh. EF Core gửi theo kiểu trong model; với thuộc tính DateTime ánh xạ vào cột datetime2(0), tham số là datetime2(7) và giữ nguyên mili giây. DateTimeOffset đi xuống là datetimeoffset.

Khi hai vế của phép so sánh khác kiểu, SQL Server đổi vế có kiểu ưu tiên thấp hơn sang kiểu cao hơn. Thứ tự là datetimeoffset, rồi datetime2, rồi datetime. Tham số datetime so với cột datetime2(0) thì tham số bị đổi, cột giữ nguyên. Tham số datetimeoffset thì ngược lại: cột NgayTao bị đổi sang datetimeoffset, và giá trị không có độ lệch được coi là +00:00. Đơn tạo lúc 01/10 23:59:59 giờ Việt Nam thành 23:59:59 UTC, tức 02/10 06:59:59 giờ Việt Nam, nằm ngoài khoảng.

Giờ Việt Nam và UTC #

BanHang lưu NgayTao theo giờ Việt Nam, UTC+7: đơn 10042 tạo lúc 11:58 là 11:58 giờ Việt Nam. Việt Nam không dùng giờ mùa hè, nên UTC+7 cố định, không có giờ lặp hay giờ mất trong năm. Quy ước này hỏng ở hai chỗ:

• Azure SQL Database luôn chạy theo UTC, và SYSDATETIME() ở đó trả giờ UTC. Một DEFAULT SYSDATETIME() trên NgayTao sẽ ghi giờ UTC lẫn với dữ liệu giờ Việt Nam, và đơn lúc 06:59 sáng 01/01/2027 giờ Việt Nam rơi vào partition năm 2026. Azure SQL Managed Instance chọn múi giờ lúc tạo instance và không đổi được sau đó.
• Dữ liệu đến từ hệ khác, như log Azure hay API đối tác, thường là UTC. Bảng lưu UTC mà báo cáo tính theo ngày Việt Nam thì ngày 01/10 là khoảng từ 30/09 17:00 đến trước 01/10 17:00 UTC.

AT TIME ZONE, có từ SQL Server 2016, đổi giữa các múi giờ; tên múi giờ lấy từ sys.time_zone_info. Bọc cột trong AT TIME ZONE thì mỗi dòng phải tính lại, và thứ tự của chỉ mục không dùng được nữa.

4. Cách giải quyết: khoảng nửa mở, mốc cùng quy ước giờ với cột #

Ô trống là không đo trong bài. BanHang chọn khoảng nửa mở. Nó không cần mốc cuối ngày nên không phụ thuộc kiểu hay độ chính xác của tham số, và cột đứng một mình nên vẫn seek.

Các bước:

1. Tính hai mốc từ một ngày: đầu ngày và đầu ngày hôm sau. Hai mốc đều là nửa đêm, nên kiểu datetime cũng biểu diễn chính xác.
2. Đưa hai mốc về cùng quy ước giờ với cột trước khi gửi. Cột giờ Việt Nam nhận DateTime giờ Việt Nam không kèm độ lệch. Cột UTC nhận hai mốc đã đổi sang UTC.
3. Không bọc cột trong hàm, kể cả AT TIME ZONE.
4. Ghi giờ mới bằng cách tính từ UTC: CAST(SYSUTCDATETIME() AT TIME ZONE 'UTC' AT TIME ZONE 'SE Asia Standard Time' AS datetime2(0)) trong thủ tục, hoặc TimeZoneInfo.ConvertTimeFromUtc ở ứng dụng.
5. Bảng mới nhận thời điểm từ nhiều múi giờ lưu UTC trong datetime2(0) với tên cột nói rõ, như ThoiDiemUtc.

5. Cách cài đặt: SQL nửa mở và hai mốc tính ở C# #

Trong T-SQL, báo cáo nhận một date và tự tính hai mốc:

DECLARE @Ngay date = '20261001';

SELECT COUNT(*) AS so_don, SUM(TongTien) AS doanh_thu
FROM dbo.DonHang
WHERE NgayTao >= CAST(@Ngay AS datetime2(0))
  AND NgayTao < CAST(DATEADD(DAY, 1, @Ngay) AS datetime2(0));

Bảng lưu UTC thì đổi hai mốc, không đổi cột. Với @Ngay = '20261001', hai mốc ra 2026-09-30 17:00:00 và 2026-10-01 17:00:00:

DECLARE @TuUtc datetime2(0) = CAST(CAST(@Ngay AS datetime2(0))
            AT TIME ZONE 'SE Asia Standard Time' AT TIME ZONE 'UTC' AS datetime2(0)),
        @DenUtc datetime2(0) = CAST(CAST(DATEADD(DAY, 1, @Ngay) AS datetime2(0))
            AT TIME ZONE 'SE Asia Standard Time' AT TIME ZONE 'UTC' AS datetime2(0));

Ở C#, hai mốc lấy từ DateOnly, mốc DateTimeOffset đổi về giờ Việt Nam trước khi gửi:

var ngay = new DateOnly(2026, 10, 1);
var baoCao = await conn.QuerySingleAsync<(int SoDon, decimal DoanhThu)>(
    "SELECT COUNT(*), ISNULL(SUM(TongTien), 0) FROM dbo.DonHang WHERE NgayTao >= @Tu AND NgayTao < @Den;",
    new { Tu = ngay.ToDateTime(TimeOnly.MinValue), Den = ngay.AddDays(1).ToDateTime(TimeOnly.MinValue) });

// API nhận DateTimeOffset: đổi về giờ Việt Nam không kèm độ lệch, cùng quy ước với NgayTao
var vn = TimeZoneInfo.FindSystemTimeZoneById("SE Asia Standard Time");
DateTime GioVn(DateTimeOffset t) => TimeZoneInfo.ConvertTime(t, vn).DateTime;

// NgayTao giờ Việt Nam tính từ UTC, không từ DateTime.Now của máy chạy API
var ngayTao = TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, vn);

// Dapper gửi DateTime dạng datetime2 thay cho datetime: gọi một lần lúc khởi động
SqlMapper.AddTypeMap(typeof(DateTime), DbType.DateTime2);

AddTypeMap phải chạy trước truy vấn đầu tiên. Dapper sinh sẵn bộ đặt tham số cho mỗi dạng câu lệnh, nên gọi muộn thì câu đã chạy vẫn gửi datetime cho tới khi gọi SqlMapper.PurgeQueryCache(). Khoảng nửa mở đúng cả khi không đổi ánh xạ, vì hai mốc nửa đêm không bị làm tròn.

Chương trình NgayGio.cs chạy mọi trường hợp trên LocalDB: ba đơn mẫu trong bảng tạm #Don, rồi bốn cách lọc ngày trên dbo.DonHang 218.000 đơn do KieuCot.cs ở bài chọn kiểu cột tạo, mỗi đơn cách nhau 2 phút. Logical reads lấy từ SET STATISTICS IO qua sự kiện InfoMessage. Hai dòng "cột UTC" coi NgayTao của bảng thử là giờ UTC. Môi trường: .NET 10.0.12, Dapper 2.1.89, Microsoft.EntityFrameworkCore.SqlServer 10.0.12, SQL Server 2019 LocalDB 15.0.4382, Intel Core Ultra 5 125U, database Kumeo_D. Dòng #:property PublishAot=false tắt AOT mà file-based app bật mặc định, vì Dapper và EF Core sinh code lúc chạy. Chạy dotnet run KieuCot.cs rồi dotnet run NgayGio.cs.

#:package Microsoft.EntityFrameworkCore.SqlServer@10.0.12
#:package Dapper@2.1.89
#:property PublishAot=false
// Đếm đơn theo ngày: kiểu tham số ngày giờ mà Dapper và EF Core gửi xuống, BETWEEN với mốc .999,
// khoảng nửa mở, tham số DateTimeOffset, và giá của việc đổi múi giờ trên cột thay vì trên mốc.
// Cần dbo.DonHang (datetime2(0), 218.000 dòng) do KieuCot.cs tạo trong cùng database.
using System.Data;
using System.Text.RegularExpressions;
using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

using var conn = new SqlConnection(Db.Cs);
conn.Open();
conn.Execute("ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;");

// ---------- 1. Ba đơn, một đơn tạo đúng nửa đêm ----------
conn.Execute("""
    CREATE TABLE #Don (DonHangId bigint NOT NULL, NgayTao datetime2(0) NOT NULL, TongTien decimal(18, 2) NOT NULL);
    INSERT #Don VALUES (1, '2026-10-01T00:00:00', 100000), (2, '2026-10-01T23:59:59', 200000), (3, '2026-10-02T00:00:00', 300000);
    """);
const string KieuVaGiaTri = "SELECT CAST(SQL_VARIANT_PROPERTY(@Den, 'BaseType') AS varchar(20)), CAST(@Den AS datetime2(7));";
var tu = new DateTime(2026, 10, 1);
var den = new DateTime(2026, 10, 1, 23, 59, 59, 999);

var k1 = conn.QuerySingle<(string Kieu, DateTime GiaTri)>(KieuVaGiaTri, new { Den = den });
var sai = conn.QuerySingle<(int SoDon, decimal DoanhThu)>(
    "SELECT COUNT(*), ISNULL(SUM(TongTien), 0) FROM #Don WHERE NgayTao BETWEEN @Tu AND @Den;", new { Tu = tu, Den = den });
Console.WriteLine($"Dapper DateTime         -> {k1.Kieu}, tới server: {k1.GiaTri:yyyy-MM-dd HH:mm:ss.fffffff}; BETWEEN: {sai.SoDon} đơn, {sai.DoanhThu}");

var p = new DynamicParameters();
p.Add("Den", den, DbType.DateTime2);
var k2 = conn.QuerySingle<(string Kieu, DateTime GiaTri)>(KieuVaGiaTri, p);
Console.WriteLine($"Dapper DbType.DateTime2 -> {k2.Kieu}, tới server: {k2.GiaTri:yyyy-MM-dd HH:mm:ss.fffffff}");

// Khoảng nửa mở: hai mốc đều là nửa đêm, kiểu datetime biểu diễn chính xác.
var ngay = new DateOnly(2026, 10, 1);
var dung = conn.QuerySingle<(int SoDon, decimal DoanhThu)>(
    "SELECT COUNT(*), ISNULL(SUM(TongTien), 0) FROM #Don WHERE NgayTao >= @Tu AND NgayTao < @Den;",
    new { Tu = ngay.ToDateTime(TimeOnly.MinValue), Den = ngay.AddDays(1).ToDateTime(TimeOnly.MinValue) });
Console.WriteLine($"Nửa mở [01/10, 02/10)   -> {dung.SoDon} đơn, {dung.DoanhThu}");

// Cùng khoảng nửa mở, nhưng mốc là DateTimeOffset +07:00 như API nhận từ client.
var vnOffset = TimeSpan.FromHours(7);
var tuDto = new DateTimeOffset(2026, 10, 1, 0, 0, 0, vnOffset);
var lech = conn.QuerySingle<(string Kieu, int SoDon, decimal DoanhThu)>("""
    SELECT CAST(SQL_VARIANT_PROPERTY(@Tu, 'BaseType') AS varchar(20)), COUNT(*), ISNULL(SUM(TongTien), 0)
    FROM #Don WHERE NgayTao >= @Tu AND NgayTao < @Den;
    """, new { Tu = tuDto, Den = tuDto.AddDays(1) });
Console.WriteLine($"Nửa mở, DateTimeOffset  -> {lech.Kieu}; {lech.SoDon} đơn, {lech.DoanhThu}");

// Sửa: đổi mốc DateTimeOffset về giờ Việt Nam không kèm độ lệch, cùng quy ước với cột NgayTao.
var vn = TimeZoneInfo.FindSystemTimeZoneById("SE Asia Standard Time");
DateTime GioVn(DateTimeOffset t) => TimeZoneInfo.ConvertTime(t, vn).DateTime;
var sua = conn.QuerySingle<(int SoDon, decimal DoanhThu)>(
    "SELECT COUNT(*), ISNULL(SUM(TongTien), 0) FROM #Don WHERE NgayTao >= @Tu AND NgayTao < @Den;",
    new { Tu = GioVn(tuDto), Den = GioVn(tuDto.AddDays(1)) });
Console.WriteLine($"Nửa mở, đổi về giờ VN   -> {sua.SoDon} đơn, {sua.DoanhThu}");

// Giờ Việt Nam tính từ UTC, không phụ thuộc múi giờ của máy chạy API.
var utc = new DateTime(2026, 10, 2, 4, 58, 0, DateTimeKind.Utc);
Console.WriteLine($"UTC {utc:yyyy-MM-dd HH:mm} -> giờ Việt Nam {TimeZoneInfo.ConvertTimeFromUtc(utc, vn):yyyy-MM-dd HH:mm}");

// ---------- 2. EF Core gửi DateTime theo kiểu trong model ----------
using (var db = new BanHangDb()) db.DonHang.Count(d => d.NgayTao >= tu && d.NgayTao <= den);
var efText = conn.QuerySingle<string>("""
    SELECT TOP (1) st.text
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    WHERE st.text LIKE N'(@%FROM `[DonHang`]%' ESCAPE N'`';
    """);
Console.WriteLine($"EF Core DateTime        -> {efText[..(efText.IndexOf(")SELECT") + 1)]}");

// ---------- 3. Lọc một ngày trên 218.000 đơn: logical reads theo cách viết ----------
var reads = 0L;
conn.InfoMessage += (_, e) =>
{
    foreach (Match m in Regex.Matches(e.Message, @"logical reads (\d+)")) reads += long.Parse(m.Groups[1].Value);
};
conn.Execute("SET STATISTICS IO ON;");
var cachViet = new (string Ten, string Sql)[]
{
    ("Nửa mở trên cột", "SELECT COUNT(*) FROM dbo.DonHang WHERE NgayTao >= @Tu AND NgayTao < @Den;"),
    ("CAST(cột AS date) = @Ngay", "SELECT COUNT(*) FROM dbo.DonHang WHERE CAST(NgayTao AS date) = @Ngay;"),
    ("Cột UTC, AT TIME ZONE trên cột", """
        SELECT COUNT(*) FROM dbo.DonHang
        WHERE CAST(NgayTao AT TIME ZONE 'UTC' AT TIME ZONE 'SE Asia Standard Time' AS date) = @Ngay;
        """),
    ("Cột UTC, đổi múi giờ ở hai mốc", "SELECT COUNT(*) FROM dbo.DonHang WHERE NgayTao >= @TuUtc AND NgayTao < @DenUtc;"),
};
var thamSo = new DynamicParameters();
thamSo.Add("Tu", ngay.ToDateTime(TimeOnly.MinValue));
thamSo.Add("Den", ngay.AddDays(1).ToDateTime(TimeOnly.MinValue));
thamSo.Add("Ngay", ngay.ToDateTime(TimeOnly.MinValue), DbType.Date);
thamSo.Add("TuUtc", TimeZoneInfo.ConvertTimeToUtc(ngay.ToDateTime(TimeOnly.MinValue), vn));        // 30/09 17:00
thamSo.Add("DenUtc", TimeZoneInfo.ConvertTimeToUtc(ngay.AddDays(1).ToDateTime(TimeOnly.MinValue), vn)); // 01/10 17:00
foreach (var (ten, sql) in cachViet)
{
    reads = 0;
    var soDon = conn.QuerySingle<int>(sql, thamSo);
    Console.WriteLine($"{ten,-32} {soDon,4} đơn, {reads,4} logical reads");
}
conn.Execute("SET STATISTICS IO OFF;");

// Đổi ánh xạ mặc định của Dapper cho DateTime. Ứng dụng thật gọi một lần lúc khởi động, trước truy vấn đầu tiên;
// ở đây phải xóa cache vì Dapper đã sinh sẵn bộ đặt tham số cho câu cùng dạng ở trên.
SqlMapper.AddTypeMap(typeof(DateTime), DbType.DateTime2);
SqlMapper.PurgeQueryCache();
var k3 = conn.QuerySingle<(string Kieu, DateTime GiaTri)>(KieuVaGiaTri, new { Den = den });
Console.WriteLine($"Sau AddTypeMap(DateTime, DateTime2) -> {k3.Kieu}, tới server: {k3.GiaTri:yyyy-MM-dd HH:mm:ss.fffffff}");

class DonHangEf { public long DonHangId { get; set; } public DateTime NgayTao { get; set; } }

class BanHangDb : DbContext
{
    public DbSet<DonHangEf> DonHang => Set<DonHangEf>();
    protected override void OnConfiguring(DbContextOptionsBuilder o) => o.UseSqlServer(Db.Cs);
    protected override void OnModelCreating(ModelBuilder b) => b.Entity<DonHangEf>(e =>
    {
        e.ToTable("DonHang");
        e.HasKey(d => new { d.NgayTao, d.DonHangId });
        e.Property(d => d.NgayTao).HasPrecision(0);
    });
}

static class Db
{
    public const string Cs = @"Server=(localdb)\MSSQLLocalDB;Database=Kumeo_D;Integrated Security=true;TrustServerCertificate=true";
}

6. Chứng minh: 2 đơn với mọi kiểu tham số, 7 page thay vì 652 #

Kết quả NgayGio.cs trên ba đơn mẫu:

Tiêu chí đầu ở mục 2 đạt: khoảng nửa mở ra 2 đơn, 300.000 đồng với mốc datetime, và với mốc DateTimeOffset sau khi đổi về giờ Việt Nam. Khoảng nửa mở không cứu được mốc DateTimeOffset gửi thẳng: lỗi đó nằm ở quy ước giờ, không ở dấu so sánh.

Lọc ngày 01/10 trên 218.000 đơn, mỗi cách ra 720 đơn:

Tiêu chí thứ hai đạt: ba cách giữ cột đứng một mình đều seek, dưới 10 page. Bọc cột trong AT TIME ZONE đọc 652 page, gần bằng cả chỉ mục IX_DonHang_KhachHang 647 page lá: engine quét chỉ mục hẹp nhất có NgayTao rồi tính lại từng dòng. CAST(NgayTao AS date) là ngoại lệ trình tối ưu vẫn seek được, đổi lại nó chỉ hợp khi ngày cần lọc trùng quy ước giờ của cột.

Tiêu chí thứ ba: TimeZoneInfo.ConvertTimeFromUtc đổi 2026-10-02 04:58 UTC thành 2026-10-02 11:58 giờ Việt Nam, giờ tạo của đơn 10042, không đọc múi giờ của máy chạy.

7. Kết luận #

Lọc theo ngày viết nửa mở, >= đầu ngày AND < đầu ngày sau, với hai mốc cùng quy ước giờ của cột. Mốc cuối ngày .997 hay .999 phụ thuộc kiểu tham số, còn mốc nửa đêm thì không.

Trong dự án .NET của bạn:

• Tìm và xóa mọi mốc 23:59:59.997, .999 hay AddTicks(-1) trong code báo cáo, thay bằng ngay.AddDays(1).ToDateTime(TimeOnly.MinValue) làm mốc trên, so bằng <.
• Đổi DateTimeOffset từ API về đúng quy ước của cột bằng TimeZoneInfo.ConvertTime trước khi đưa vào tham số.
• Với Dapper, gọi SqlMapper.AddTypeMap(typeof(DateTime), DbType.DateTime2) lúc khởi động.
• Tính NgayTao từ DateTime.UtcNow hoặc TimeProvider.GetUtcNow() qua TimeZoneInfo, không dùng DateTime.Now của máy chạy API.

Những chỗ hay hiểu sai #

• "BETWEEN ... '23:59:59.997' lấy đủ một ngày." Chỉ với cột và tham số datetime. Với datetime2, mốc đó làm sót hoặc đếm trùng.
• "Dapper gửi DateTime theo kiểu của cột." Dapper và AddWithValue gửi datetime; EF Core gửi theo kiểu trong model.
• "DateTimeOffset an toàn hơn DateTime." So với cột không có độ lệch, nó làm cột bị coi là UTC.
• "datetimeoffset lưu múi giờ." Nó chỉ lưu độ lệch tại thời điểm ghi, không lưu tên múi giờ.

Đọc tiếp #

• Bài trước: Tìm "nguyen" ra "Nguyễn" mà vẫn Index Seek. Bài tiếp: Kiểu tham số từ .NET làm truy vấn quét cả bảng.
• Giờ UTC trong API restore của Azure: Azure SQL.
• NgayTao còn là cột phân vùng theo năm của DonHang: Tách đơn cũ khỏi SSD bằng filegroup và partition.

Nguồn #

• datetime
• datetime2
• datetimeoffset
• AT TIME ZONE
• Data type precedence
• Time zones in Azure SQL Managed Instance
• SqlClient: SQL Server data type mappings
• Dapper: README, ánh xạ kiểu