آموزش کوئری تاریخ و زمان در SQL اکسس با DateAdd و DateDiff

فیلتر تاریخ در فاکتورها، حضور و غیاب و داشبوردهای عملیاتی بسیار استفاده می‌شود، اما یکی از منابع پنهان گزارش اشتباه است. ممکن است رکورد ثبت‌شده در ساعت ۱۶:۳۰ روز پایانی ماه از گزارش حذف شود؛ چون شرط SQL تاریخ پایان را ساعت صفر بامداد در نظر گرفته است. قالب‌های متفاوت تاریخ هم می‌توانند نتیجه را مبهم‌تر کنند.

در این درس یاد می‌گیریم اکسس تاریخ و زمان را چگونه مقایسه می‌کند، چطور بازه کامل را بدون حذف تراکنش‌های آخر روز فیلتر کنیم و توابع Date و DateAdd و DateDiff چه کاربردی دارند. مثال‌ها ادامه منطقی WHERE و کوئری پارامتری هستند و با DAO در یک پایگاه واقعی اکسس قابل اجرا خواهند بود.

مفهوم و محدوده کاربرد

فیلد Date/Time در Access می‌تواند تاریخ تقویمی و ساعت همان روز را هم‌زمان نگهداری کند. دو رکورد که در Datasheet با یک تاریخ کوتاه دیده می‌شوند، ممکن است به دلیل ساعت‌های متفاوت در مقایسه SQL نتیجه یکسان نداشته باشند. شرط <= #2026-01-31# لزوماً تراکنش‌های بعدازظهر آخر ژانویه را شامل نمی‌شود.

بازه نیمه‌باز روش مناسب‌تری است: شروع با >= و انتها با <. برای ژانویه از OrderDate >= DateSerial(2026,1,1) و OrderDate < DateSerial(2026,2,1) استفاده کنید. این شرط همه ساعت‌های روز ۳۱ ژانویه را می‌پذیرد و از ابتدای فوریه به بعد را حذف می‌کند. وجود ثانیه یا کسری از ثانیه باعث حذف ناخواسته پایان ماه نخواهد شد.

تابع Date() تاریخ امروز را برمی‌گرداند. DateAdd(interval,number,date) تاریخ را جابه‌جا می‌کند و DateDiff(interval,start,end) تعداد مرزهای زمانی واحد مشخص را می‌شمارد. برای نمونه DateDiff(“yyyy”,BirthDate,Date()) لزوماً سن کامل یک فرد نیست، زیرا مرز سال را می‌شمارد نه گذشتن روز تولد را. کاربرد تابع باید با قاعده گزارش هماهنگ باشد.

داده‌های نمونه و فرض‌ها

جدول Orders شامل OrderID و CustomerID و OrderDate و DueDate و TotalAmount و Status در نظر بگیرید. سفارش‌هایی در ساعت صفر روز اول ژانویه، ساعت ۲۳:۵۹ آخر ژانویه و ساعت صفر اول فوریه و سفارشی با DueDate از نوع Null ایجاد کنید. این رکوردها مرزهای زمانی را آشکار می‌کنند. اگر تاریخ از سامانه دیگر وارد می‌شود، قرارداد منطقه زمانی را پیش از آزمایش مشخص کنید.

ساختار دستور و تفسیر آن

در متن SQL اکسس تاریخ ثابت معمولاً با علامت # نوشته می‌شود؛ اما رشته دارای قالب محلی ممکن است نادرست تفسیر شود. برای کوئری فراخوانی‌شده از VBA، پارامتر نوع‌دار DateTime از چسباندن متن تاریخ مطمئن‌تر است. در مثال ثابت نیز DateSerial(year,month,day) معنی تاریخ موردنظر را روشن می‌کند.

در توابع تاریخ اکسس، شناسه بازه به‌صورت رشته نوشته می‌شود. DateAdd(“d”,7,Date()) یعنی هفت روز بعد و DateDiff(“d”,StartDate,EndDate) مرزهای روز را می‌شمارد. کد SQL Server را بدون تبدیل نحو آرگومان‌ها به اکسس منتقل نکنید؛ شباهت نام توابع به معنی یکسان بودن تمام قواعد نیست.

SELECT OrderID, OrderDate, TotalAmount
FROM Orders
WHERE OrderDate >= DateSerial(2026, 1, 1)
  AND OrderDate < DateSerial(2026, 2, 1)
ORDER BY OrderDate, OrderID;

مثال‌های گام‌به‌گام

سه مثال بعدی بازه کامل ماه، پنجره زمانی متحرک و سفارش‌های دارای تأخیر را پوشش می‌دهند. انتخاب باز یا بسته بودن هر مرز عمدی است؛ فقط پس از تعیین اینکه لحظه شروع و پایان باید عضو گزارش باشند یا نه، شرط را تغییر دهید.

گزارش کامل ماه

رکورد عصر ۳۱ ژانویه انتخاب می‌شود اما رکورد اول فوریه حذف خواهد شد. برخلاف شرط پایان شامل در نیمه‌شب روز آخر، این مرز بخش بزرگی از روز آخر را از دست نمی‌دهد. همین الگو برای هفته یا بازه مالی نیز قابل استفاده است.

SELECT OrderID, OrderDate FROM Orders WHERE OrderDate >= DateSerial(2026,1,1) AND OrderDate < DateSerial(2026,2,1);

سفارش‌های هفت تاریخ تقویمی اخیر

این شرط امروز و شش روز تقویمی قبل از آن را با تمام ساعت‌ها پوشش می‌دهد. هدف آن «۱۶۸ ساعت گذشته» نیست. اگر گزارش دقیقاً ۱۶۸ ساعت متوالی می‌خواهد، مرز را با Now() بسازید و تفاوت دو تعریف را برای استفاده‌کنندگان توضیح دهید.

SELECT OrderID, OrderDate FROM Orders WHERE OrderDate >= DateAdd("d", -6, Date()) AND OrderDate < DateAdd("d", 1, Date());

سفارش‌های معوق و تکمیل‌نشده

این شرط سفارش‌هایی را که سررسیدشان قبل از امروز است و وضعیت Completed ندارند انتخاب می‌کند. تاریخ سررسید Null از مقایسه عبور نمی‌کند و برای آن باید گزارش ناقص‌بودن داده مستقل ساخت. وضعیت Null را نیز بر اساس سیاست کسب‌وکار جدا بررسی کنید.

SELECT OrderID, DueDate, Status FROM Orders WHERE DueDate < Date() AND Status <> "Completed";

اجرای کوئری با VBA در اکسس

در VBA یک QueryDef موقت با دو پارامتر DateTime می‌سازیم: آغاز شامل و پایان غیرشامل. مرز ماه بعد با DateSerial تولید و مقادیر واقعی نوع Date به پارامترها داده می‌شوند؛ بنابراین رشته مبهم تاریخ منطقه‌ای وارد SQL نمی‌شود. چون فقط رکوردها خوانده می‌شوند، dbOpenSnapshot انتخاب مناسبی است.

این کد در ماژول Access و با جدول Orders اجرا می‌شود. نوع OrderDate باید واقعاً Date/Time باشد، نه متن تاریخ محلی. اگر در سامانه داده‌ها با UTC ذخیره شده‌اند، تبدیل منطقه زمانی را در مرحله مشخص و مستند انجام دهید؛ نیمه‌شب محلی با نیمه‌شب UTC یکی نیست و می‌تواند گزارش روزانه را تغییر دهد.

Option Explicit
Public Sub ShowJanuaryOrders()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset
    On Error GoTo Fail
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef("", _
        "PARAMETERS [pStart] DateTime, [pEndExclusive] DateTime; " & _
        "SELECT OrderID, OrderDate FROM Orders " & _
        "WHERE OrderDate >= [pStart] AND OrderDate < [pEndExclusive] " & _
        "ORDER BY OrderDate, OrderID;")
    qdf.Parameters("pStart") = DateSerial(2026, 1, 1)
    qdf.Parameters("pEndExclusive") = DateSerial(2026, 2, 1)
    Set rs = qdf.OpenRecordset(dbOpenSnapshot)
    Do Until rs.EOF
        Debug.Print rs!OrderID, rs!OrderDate
        rs.MoveNext
    Loop
CleanUp:
    If Not rs Is Nothing Then rs.Close
    Set rs = Nothing
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub
Fail:
    Debug.Print Err.Number, Err.Description
    Resume CleanUp
End Sub

کنترل نتیجه و شرایط مرزی

رکوردی دقیقاً روی مرز آغاز، رکوردی یک ثانیه پیش از مرز پایان و رکوردی دقیقاً روی مرز پایان قرار دهید. دو رکورد اول باید پذیرفته و سومی حذف شود. اگر OrderDate اجازه Null دارد، بررسی کنید که شرط عادی بازه آن را نمی‌پذیرد. همین آزمون را در ماه ۲۸ یا ۲۹ روزه نیز تکرار کنید تا وابستگی احتمالی به تعداد روز ثابت آشکار شود.

خطاهای رایج و راه‌حل

خطاهای کوئری تاریخ معمولاً به ساعت مخفی، تبدیل متن مبهم یا تعریف‌نشدن دقیق مرز بازه مربوط می‌شوند. شرط را با زمان‌های معلوم مقایسه کنید:

بهترین روش‌ها برای کوئری قابل اعتماد

برای بازه‌ای که کاربر از فرم انتخاب می‌کند، پارامترهای نوع‌دار DateTime بنویسید و مقدار Date واقعی اختصاص دهید. تاریخ را با قالب محلی به رشته SQL نچسبانید؛ برای نمونه 04/05 در یک محیط ممکن است چهارم مه و در محیطی دیگر پنجم آوریل تفسیر شود.

تا جای ممکن روی فیلد تاریخ ایندکس‌شده تابع اجرا نکنید. به جای DateValue(OrderDate)=Date() می‌توان OrderDate>=Date() و OrderDate<DateAdd(“d”,1,Date()) را نوشت. چنین شرط بازه‌ای ممکن است استفاده مؤثرتری از ایندکس فراهم کند، ولی نتیجه باید با داده واقعی اندازه‌گیری شود.

مشخص کنید روز و هفته و ماه گزارش بر اساس ساعت محلی کسب‌وکار تعریف می‌شوند یا منطقه زمانی سرور. برای گزارش‌هایی که چند منطقه جغرافیایی را پوشش می‌دهند، تبدیل زمان بخشی از قرارداد داده است. فیلد Date/Time به‌تنهایی سیاست کامل منطقه زمانی را ذخیره نمی‌کند.

DateDiff تعداد عبور از مرز واحد زمانی را می‌شمارد. DateDiff(“yyyy”,BirthDate,Date()) برای محاسبه دقیق سن کامل کافی نیست؛ ممکن است سال نو شده باشد اما تولد فرد هنوز نرسیده باشد. در تصمیم حساس مانند منابع انسانی یا امور حقوقی، روز و ماه تولد را نیز صریح بررسی کنید.

در گزارش‌های سال مالی یا تاریخ شمسی ابتدا مرز واقعی تاریخ را با یک لایه تبدیل تقویم معتبر تعیین کنید؛ سپس تاریخ میلادی متناظر را به‌صورت پارامتر نوع Date بدهید. DateSerial به‌خودی‌خود تقویم هجری شمسی را پیاده نمی‌کند و تغییر قالب نمایش، مبنای محاسبات تاریخ را تغییر نخواهد داد.

آزمایشگاه تمرین

گزارش سفارش ماهانه با دو پارامتر pStart و pEndExclusive بسازید. آن را با سفارش‌های اول ژانویه ساعت صفر، ۳۱ ژانویه ساعت ۲۳:۵۹ و اول فوریه ساعت صفر آزمایش کنید. قبل از اجرا OrderIDهای مورد انتظار را بنویسید. سپس گزارش فوریه را بدون دست‌کاری متن SQL و فقط با تغییر مقدار پارامترها اجرا کنید.

گزارش سفارش‌های معوق بسازید که سفارش Completed را حذف و DueDate ناموجود را از تاریخ آینده جدا کند. کوئری دیگری برای سررسید هفت روز آینده ایجاد کنید. نتیجه را نزدیک نیمه‌شب کنترل و مستند کنید «هفت روز آینده» یعنی هفت تاریخ تقویمی یا دقیقاً ۱۶۸ ساعت متوالی. این دو تعریف همیشه خروجی یکسان ندارند.

درس‌های مرتبط

در درس WHERE منطق مقایسه را می‌آموزید، QueryDef ورودی بازه را با نوع درست می‌دهد و ORDER BY ترتیب زمانی ثابت ایجاد می‌کند. ترکیب این سه مهارت به گزارش‌های تاریخ قابل اعتماد منتهی می‌شود.

• کوئری پارامتری با QueryDef: کوئری پارامتری در SQL اکسس با QueryDef و VBA
• مرتب‌سازی ORDER BY در اکسس: آموزش مرتب‌سازی نتایج کوئری با ORDER BY در SQL اکسس
• کاربرد WHERE در SQL اکسس: آموزش SQL در Microsoft Access: کاربرد جزء WHERE در SQL

جمع‌بندی

کوئری تاریخ در SQL اکسس زمانی مطمئن است که مرز دوره صریح تعریف شود. برای Date/Time دارای ساعت از بازه نیمه‌باز استفاده، پارامتر DateTime نوع‌دار ارسال و به جای حدس درباره متن محلی، از DateSerial و DateAdd کمک بگیرید.

لحظه آغاز و انتهای بازه، تاریخ ناموجود و فرض منطقه زمانی را جدا آزمایش کنید. گزارش تاریخ فقط زمانی قابل اتکا است که این مرزها با داده واقعی اثبات شوند، نه اینکه صرفاً تاریخ‌های نمایش‌داده‌شده در Datasheet درست به نظر برسند.