[RaggiTech]

كاتب الموضوع : AhmedEssawy

الكود مشروح باستفاضة ضمن الخطوات .

كود :

CREATE FUNCTION dbo.DateTimeList (
@startDate AS DATETIME,
@endDate AS DATETIME,
@interval AS INT = 1, -- this is the interval per daypart default is 1
@daypart AS VARCHAR(10) = 'day' -- the default is by day
)
RETURNS @DateTimeTable TABLE (
DateList DATETIME
)
AS
BEGIN
/*******************************************************/
/* */
/* This function will return a table of date times */
/* based off of a specified part and */
/* interval. For instance, if you need a list of */
/* date times for every hour between a particular */
/* start and end date. The function supports */
/* day parts from year down to minute */
/*******************************************************/
/* */
/* Input Parameters: */
/* */
/* StartDate datetime Start Date of range. */
/* REQUIRED parameter. */
/* */
/* EndDate datetime End Date of range. */
/* REQUIRED parameter. */
/* */
/* Interval int Integer value representing */
/* the amount to increment */
/* defaults to 1. */
/* */
/* DayPart varchar String value representing*/
/* the day part to use */
/* valid values are: */
/* 'year' */
/* 'quarter' */
/* 'month' */
/* 'week' */
/* 'day' */
/* 'hour' */
/* 'minute' */
/* */
/*******************************************************/
/* */
/* Returns: */
/* */
/* table Table of date time values by day part*/
/* within the input time period. */
/* */
/*******************************************************/
/* */
/* Usage: */
/* */
/* exec DateTimeList */
/* @StartDate = 'mm/dd/yyyy', */
/* @EndDate = 'mm/dd/yyyy', */
/* @Interval = i */
/* @DayPart = 'day part needed' */
/* */
/*******************************************************/
DECLARE @lenDiff TINYINT
DECLARE @dateDiff INT
/*******************************************************/
/* */
/* Setup date difference based off of entered day part.*/
/* Use the length of the resulting date difference to */
/* determine how many digits will be used in the */
/* cross join. */
/* setup a table of digits for use in the cross join */
/* */
/*******************************************************/
SELECT @dateDiff = CASE @daypart
WHEN 'year' THEN DATEDIFF(yy,@startDate,@endDate)
WHEN 'quarter' THEN DATEDIFF(qq,@startDate,@endDate)
WHEN 'month' THEN DATEDIFF(mm,@startDate,@endDate)