Hàm DATEDIFF trong SQL Server

Datediff trong SQL là gì? Bài viết sẽ hướng dẫn bạn cách dùng hàm Datediff trong SQL Server.

Hàm DATEDIFF() trong SQL Server là một công cụ mạnh mẽ được sử dụng để tính toán chênh lệch giữa hai ngày hoặc giờ. Hàm này trả về một số nguyên biểu thị số ranh giới ngày hoặc giờ giữa các ngày được chỉ định, dựa trên ngày được chỉ định. Hàm này rất cần thiết cho các tác vụ liên quan đến việc so sánh ngày, tính toán thời lượng hoặc đo khoảng thời gian trong SQL Server.

Về cơ bản, cách dùng hàm DATEDIFF của SQL không quá khó. Dưới đây là hướng dẫn chi tiết.

Mô tả

DATEDIFF trong SQL Server là một hàm cơ bản được dùng để triển khai các phép tính dựa trên ngày tháng. Nó cho kết quả là một giá trị số nguyên theo đơn vị ngày tháng như năm, tháng, ngày, phút và giây. Định nghĩa chính thức của Datediff SQL Server là tính sự khác biệt giữa hai ngày tháng.

Hàm DATEDIFF SQL Server có những đặc điểm nổi bật sau:

• Được dùng để tìm sự khác biệt giữa hai ngày tháng.
• Thuộc về danh mục hàm Date.
• Interval - khoảng thời gian, giá trị đầu tiên của ngày tháng và giá trị thứ hai của ngày tháng là 3 tham số được chấp nhận bởi hàm này.
• Thời gian có thể được bao gồm trong phần interval cùng với phần giá trị ngày tháng của hàm này.

Cú pháp

Để sử dụng hàm DATEDIFF trong SQL Server, ta dùng cú pháp như sau:

DATEDIFF(dangthoigian, thoigian1, thoigian2)

Tham số:

• dangthoigian: dạng thời gian sử dụng để tính chênh lệch giữa thoigian1 và thoigian2. Nó có thể là một trong những giá trị sau:

  Giá trị	Giải thích
  year, yyyy, yy	Năm
  quarter, qq, q	Quý
  month, mm, m	Tháng
  dayofyear	Ngày trong năm
  day, dy, y	Ngày
  week, ww, wk	Tuần
  weekday, dw, w	Ngày trong tuần
  hour, hh	Giờ
  minute, mi, n	Phút
  second, ss, s	Giây
  millisecond, ms	Milli giây

• thoigian1, thoigian2: khoảng thời gian để tính chênh lệch.

Lưu ý:

• DATEDIFF có thể được sử dụng trong các phiên bản sau của SQL Server: SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005.

Ví dụ

Hãy xem và khám phá một số ví dụ về hàm DATEDIFF trong SQL Server.

SELECT DATEDIFF(year, '2019/04/28', '2021/04/28');Result: 2SELECT DATEDIFF(yyyy, '2019/04/28', '2021/04/28');Result: 2SELECT DATEDIFF(yy, '2019/04/28', '2021/04/28');Result: 2SELECT DATEDIFF(month, '2019/01/01', '2019/04/28');Result: 3SELECT DATEDIFF(day, '2019/01/01', '2019/04/28');Result: 117SELECT DATEDIFF(hour, '2019/04/28 08:00', '2019/04/28 10:45');Result: 2SELECT DATEDIFF(minute, '2019/04/28 08:00', '2019/04/28 10:45');Result: 165

Dùng hàm DATEDIFF() để so sánh sự khác biệt giữa hai giá trị ngày tháng

Ví dụ này dùng hàm DATEDIFF để so sánh sự khác biệt giữa hai ngày tháng ở các phần khác nhau:

DECLARE 
    @start_dt DATETIME2= '2019-12-31 23:59:59.9999999', 
    @end_dt DATETIME2= '2020-01-01 00:00:00.0000000';

SELECT 
    DATEDIFF(year, @start_dt, @end_dt) diff_in_year, 
    DATEDIFF(quarter, @start_dt, @end_dt) diff_in_quarter, 
    DATEDIFF(month, @start_dt, @end_dt) diff_in_month, 
    DATEDIFF(dayofyear, @start_dt, @end_dt) diff_in_dayofyear, 
    DATEDIFF(day, @start_dt, @end_dt) diff_in_day, 
    DATEDIFF(week, @start_dt, @end_dt) diff_in_week, 
    DATEDIFF(hour, @start_dt, @end_dt) diff_in_hour, 
    DATEDIFF(minute, @start_dt, @end_dt) diff_in_minute, 
    DATEDIFF(second, @start_dt, @end_dt) diff_in_second, 
    DATEDIFF(millisecond, @start_dt, @end_dt) diff_in_millisecond;

Kết quả:

Dùng hàm DATEDIFF() với cột bảng

Ví dụ sau dùng hàm DATEDIFF() để so sánh ngày tháng vận chuyển được yêu cầu với ngày tàu chạy theo ngày và trả về kết quả đơn hàng đúng hạn hoặc muộn:

SELECT
    order_id, 
    required_date, 
    shipped_date,
    CASE
        WHEN DATEDIFF(day, required_date, shipped_date) < 0
        THEN 'Late'
        ELSE 'OnTime'
    END shipment
FROM 
    sales.orders
WHERE 
    shipped_date IS NOT NULL
ORDER BY 
    required_date;

Kết quả:

Chọn các hằng số từ date1 và date2 với giá trị âm

Dùng hàm so sánh ngày trong SQL Server và nhận chênh lệch âm giữa hai giá trị ngày tháng, tính theo ngày.

Ví dụ:

SELECT DATEDIFF(day, '2021/2/1', '2010/12/12');

Kết quả:

-3704

Chọn các hằng số từ date1 và date2 theo giờ

Dùng hàm DATEDIFF() và lấy sự khác biệt giữa hai giá trị ngày tháng, bao gồm thời gian, tính theo giờ.

Ví dụ:

SELECT DATEDIFF(hour, '2019/2/1 09:55', '2020/12/12 07:45');

Kết quả:

16318

Tóm lại, những điều cần ghi nhớ khi dùng hàm DATEDIFF trong SQL Server

• Hàm DATEDIFF xử lý các giá trị ngày tháng & thời gian dưới dạng đối số.
• Trình biên dịch phát sinh lỗi nếu thử biến định dạng không phải ngày tháng cho các đối số hàm.
• Hàm này hữu ích khi bạn cần tìm điểm khác biệt giữa các phần ngày tháng khác nhau của dấu thời gian.

Các lỗi thường gặp khi dùng hàm DATEDIFF

Khi làm việc với DATEDIFF(), bạn dễ gặp phải các vấn đề liên quan đến cú pháp, thứ tự đối số hoặc hành vi cụ thể của cơ sở dữ liệu. Sau đây là các lỗi thường gặp và cách giải quyết:

Dấu ngoặc kép không đúng

Vấn đề: Sử dụng dấu ngoặc kép quanh datepart hoặc kiểu trích dẫn không nhất quán cho các ký tự ngày.

Giải pháp: Luôn dùng trích dẫn cho datepart và sử dụng dấu ngoặc kép đơn cho ngày.

-- Sai
SELECT DATEDIFF('year', '2023-01-01', '2024-01-01');

-- Đúng
SELECT DATEDIFF(year, '2023-01-01', '2024-01-01');

Datepart không được hỗ trợ trong MySQL

Vấn đề: DATEDIFF() của MySQL chỉ hỗ trợ chênh lệch ngày.

Giải pháp: Sử dụng TIMESTAMPDIFF() cho các đơn vị chi tiết như năm hoặc tháng.

-- Đúng (MySQL)
SELECT TIMESTAMPDIFF(YEAR, '2023-01-01', '2024-01-01') AS years_difference; -- Returns 1

Vấn đề về thứ tự đối số

Vấn đề: Đảo ngược ngày bắt đầu và ngày kết thúc có thể dẫn đến kết quả tiêu cực không mong muốn.

Giải pháp: Đảm bảo ngày bắt đầu đứng đầu. Sử dụng ABS() nếu chỉ có độ lớn là quan trọng.

-- Sai
SELECT DATEDIFF(day, '2023-01-05', '2023-01-01'); -- Returns -4

-- Đúng
SELECT DATEDIFF(day, '2023-01-01', '2023-01-05'); -- Returns 4

Định dạng ngày tháng theo địa phương

Vấn đề: Định dạng ngày tháng không rõ ràng (ví dụ: MM/DD/YYYY so với DD/MM/YYYY) có thể gây ra lỗi.

Giải pháp: Sử dụng tiêu chuẩn ISO 8601 (YYYY-MM-DD) để tránh nhầm lẫn.

-- Viết đúng
SELECT DATEDIFF(day, '2023-01-01', '2023-12-31'); -- Kết quả: 364

Giá trị nằm ngoài phạm vi

Vấn đề: Sự khác biệt lớn vượt quá ±2.147.483.647 gây ra lỗi tràn. Giải pháp: Sử dụng DATEDIFF_BIG() cho các phạm vi lớn.

-- Viết đúng
SELECT DATEDIFF_BIG(day, '0001-01-01', '9999-12-31'); -- Kết quả: 2,920,862,800

Trên đây là những vấn đề cơ bản về hàm DATEDIFF() trong SQL Server, giúp bạn tính toán sự khác biệt giữa hai giá trị ngày tháng. Hi vọng bài viết hữu ích với các bạn.