راهنمای جامع مدیریت تاریخ و زمان DATETIME در SQLServer

مدیریت پیشرفته داده‌های تاریخ و زمان DATETIME در SQLServer: راهنمای جامع

مدیریت دقیق DATETIME در SQLServer برای هر توسعه‌دهنده‌ای که با پایگاه‌داده کار می‌کند، حیاتی است. در این مقاله به بررسی جامع انواع داده‌ای مرتبط و توابع پرکاربرد T-SQL برای دستکاری و استفاده موثر از اطلاعات DATETIME می‌پردازیم. این راهنما به شما کمک می‌کند تا با بهره‌گیری از تکنیک‌های بهینه، عملیات تاریخ و زمان را در پایگاه‌داده SQL Server خود به بهترین شکل مدیریت کنید.

آشنایی با انواع داده‌ای تاریخ و زمان در SQL Server

SQL Server انواع داده‌ای مختلفی برای ذخیره‌سازی تاریخ و زمان ارائه می‌دهد که هر یک ویژگی‌ها و دقت خاص خود را دارند. انتخاب نوع داده مناسب، هم بر عملکرد و هم بر فضای ذخیره‌سازی تأثیر می‌گذارد.

• DATE: فقط تاریخ (سال، ماه، روز) را ذخیره می‌کند.
• TIME: فقط زمان (ساعت، دقیقه، ثانیه، کسرهای ثانیه) را ذخیره می‌کند.
• DATETIME: شامل تاریخ و زمان، با دقت میلی‌ثانیه (3.33 میلی‌ثانیه).
• SMALLDATETIME: شامل تاریخ و زمان، با دقت دقیقه.
• DATETIME2: شامل تاریخ و زمان، با دقت قابل تنظیم (از 100 نانوثانیه تا 7 رقم).
• DATETIMEOFFSET: شامل تاریخ، زمان و اطلاعات منطقه زمانی.

دریافت تاریخ و زمان جاری

برای دریافت تاریخ و زمان کنونی سرور در SQL Server، چندین تابع در دسترس است. پرکاربردترین آنها GETDATE() و SYSDATETIME() هستند که هر یک کاربرد خاص خود را دارند.

تابع GETDATE() تاریخ و زمان جاری سیستم را به صورت DATETIME با دقت تقریبی 3 میلی‌ثانیه برمی‌گرداند:

SELECT GETDATE();

تابع SYSDATETIME() تاریخ و زمان جاری سیستم را با دقت بالاتر (معمولاً تا 100 نانوثانیه) به صورت DATETIME2 برمی‌گرداند که برای برنامه‌هایی که نیاز به دقت بالا دارند، مناسب است:

SELECT SYSDATETIME();

استخراج اجزای تاریخ و زمان

برای استخراج بخش‌های خاصی از یک مقدار DATETIME مانند سال، ماه، روز، ساعت یا دقیقه، توابع مختلفی در SQL Server وجود دارد که کاربرد وسیعی در گزارش‌گیری و فیلتر کردن داده‌ها دارند.

تابع DATEPART() یک بخش مشخص از تاریخ یا زمان را به صورت عدد صحیح برمی‌گرداند. سینتکس آن به شکل زیر است:

DATEPART (datepart, date)

به عنوان مثال، برای استخراج سال از تاریخ جاری:

SELECT DATEPART(year, GETDATE());

توابع ساده‌تر YEAR()، MONTH()، و DAY() نیز برای استخراج سال، ماه و روز به ترتیب استفاده می‌شوند:

SELECT YEAR(GETDATE()) AS CurrentYear, MONTH(GETDATE()) AS CurrentMonth, DAY(GETDATE()) AS CurrentDay;

تابع DATENAME() نام رشته‌ای (مانند نام ماه یا روز هفته) یک بخش مشخص از تاریخ را برمی‌گرداند:

SELECT DATENAME(month, GETDATE()) AS MonthName;

افزودن و کاستن بازه‌های زمانی

برای اضافه کردن یا کم کردن بازه‌های زمانی به یک تاریخ یا زمان، تابع DATEADD() ابزاری قدرتمند است. این تابع به شما امکان می‌دهد تا سال، ماه، روز، ساعت و حتی میلی‌ثانیه را به تاریخ مشخصی اضافه یا از آن کم کنید.

سینتکس تابع DATEADD() به شرح زیر است:

DATEADD (datepart, number, date)

• datepart: واحد زمانی (مانند year, month, day, hour).
• number: تعداد واحدهای زمانی برای اضافه یا کم کردن (می‌تواند مثبت یا منفی باشد).
• date: مقدار تاریخ و زمان که عملیات روی آن انجام می‌شود.

مثال: اضافه کردن 5 روز به تاریخ جاری:

SELECT DATEADD(day, 5, GETDATE()) AS DatePlus5Days;

مثال: کم کردن 2 ماه از تاریخ جاری:

SELECT DATEADD(month, -2, GETDATE()) AS DateMinus2Months;

محاسبه تفاوت بین تاریخ‌ها

برای محاسبه تفاوت بین دو تاریخ بر اساس یک واحد زمانی مشخص (مانند سال، ماه، روز)، از تابع DATEDIFF() استفاده می‌شود. این تابع بسیار مفید است برای محاسبه سن، مدت زمان سپری شده یا باقی‌مانده.

سینتکس تابع DATEDIFF() به شکل زیر است:

DATEDIFF (datepart, startdate, enddate)

• datepart: واحد زمانی برای محاسبه تفاوت (مانند year, month, day, hour).
• startdate: تاریخ شروع.
• enddate: تاریخ پایان.

مثال: محاسبه تعداد روزهای بین دو تاریخ:

SELECT DATEDIFF(day, '2023-01-01', GETDATE()) AS DaysSinceNewYear;

مثال: محاسبه تفاوت بر حسب ساعت:

SELECT DATEDIFF(hour, '2023-11-20 10:00:00', GETDATE()) AS HoursPassed;

تبدیل فرمت تاریخ و زمان

نمایش تاریخ و زمان در فرمت‌های مختلف برای گزارش‌گیری یا تبادل داده بسیار مهم است. توابع CONVERT() و FORMAT() این امکان را فراهم می‌کنند.

تابع CONVERT() یک مقدار را از یک نوع داده به نوع دیگر تبدیل می‌کند و برای DATETIME، استایل‌های مشخصی برای فرمت‌بندی دارد:

CONVERT (data_type, expression [, style])

مثال: نمایش تاریخ به فرمت ‘YYYY-MM-DD’ (استایل 23):

SELECT CONVERT(varchar, GETDATE(), 23) AS FormattedDate;

تابع FORMAT() (معرفی شده در SQL Server 2012 به بعد) انعطاف‌پذیری بیشتری در فرمت‌بندی بر اساس فرمت‌های .NET فراهم می‌کند و کار با آن ساده‌تر است:

FORMAT (value, format [, culture])

مثال: نمایش تاریخ و زمان با فرمت دلخواه:

SELECT FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss') AS CustomFormattedDateTime;

مثال: نمایش تاریخ به صورت نام کامل ماه و سال با فرهنگ فارسی:

SELECT FORMAT(GETDATE(), 'dd MMMM yyyy', 'fa-IR') AS PersianDate;

نکات کاربردی و بهترین شیوه‌ها

• برای دقت بالا، از DATETIME2 و SYSDATETIME() استفاده کنید.
• همیشه تاریخ و زمان را با فرمت‌های ایزو (مانند ‘YYYY-MM-DD HH:MM:SS’) ذخیره کنید تا از ابهام در مناطق زمانی مختلف جلوگیری شود.
• از توابع تاریخ و زمان در شرط‌های WHERE با دقت استفاده کنید تا از اسکن کامل جداول جلوگیری شود (به خصوص اگر ایندکس روی ستون تاریخ دارید).
• برای SQL Server 2012 به بالا، FORMAT() را به دلیل انعطاف‌پذیری و خوانایی بهتر ترجیح دهید.

با به کارگیری این توابع و تکنیک‌ها، می‌توانید به طور موثر داده‌های تاریخ و زمان را در SQL Server مدیریت کرده و گزارش‌های دقیق‌تری ایجاد کنید. این روش‌ها به شما کمک می‌کنند تا کنترل کاملی بر روی نحوه نمایش و پردازش اطلاعات زمانی در پایگاه‌داده خود داشته باشید و عملکرد سیستم را بهبود بخشید.