How would you rate this article?

Rating: 2 user(s) have rated this article Average rating: 5.0
Posted by: retro
Date: 19/02/2009
Category: Windows
Views: this article has been read 951 times

Recent Articles Get the RSS feed

Start selling online with Nochex merchant services
(26/06/2010)

Nochex merchant accounts provide you with everything you need to accept payments on your web site. With no monthly fees and support for a number of ecommerce solutions, including nopCommerce, it has never been easier to start selling online!

SCA Web site now live
(25/05/2010)

Check out our latest project for community interest company S.C.A.

Microsoft .NET 4.0 Hosting now available
(25/05/2010)

We are pleased to announce support for version 4.0 of the .NET Framework on all of our hosting plans.

Aerial Spares Web Site Now Live
(11/02/2010)

We have just completed development of a new web site for UK based Aerial Spares.

nopCommerce User Guide now available
(11/02/2010)

Today sees the release of the official nopCommerce user guide. It explains every part of the application in detail and includes a getting started guide so you can get up and running quickly.

read more read more

Using SQL to calculate dates in range
The UDF's listed in this article can be used to return the list of dates between two dates. A common requirement for this is when you are storing just a start date and end date for a specific event, and need to query the individual dates within the range. An example would be to show all events that occur on a Friday.

 

CREATE FUNCTION fnGetDatesInRange
(
      @FromDate  datetime,
      @ToDate   datetime
)
RETURNS @DateList TABLE (Dt datetime)
AS
BEGIN
      DECLARE @TotalDays int
      DECLARE @DaysCount int
      SET @TotalDays =  DATEDIFF(dd,@FromDate,@ToDate)
      SET @DaysCount = 0
     
  WHILE @TotalDays >= @DaysCount           
  BEGIN 
    INSERT INTO @DateList
    SELECT (@ToDate - @DaysCount) AS DAT
    
    SET @DaysCount = @DaysCount + 1
  END
  RETURN
END  
     
 

If you wanted to change this to only return working days (based on a Monday-Friday working week) you can use the following:

 CREATE FUNCTION fnGetWorkingDatesInRange
(
      @FromDate  datetime,
      @ToDate   datetime
)
RETURNS @DateList TABLE (Dt datetime)
AS
BEGIN
      DECLARE @TotalDays int
      DECLARE @DaysCount int
      DECLARE @DayName  nvarchar(10)
      SET @TotalDays =  DATEDIFF(dd,@FromDate,@ToDate)
      SET @DaysCount = 0
     
  WHILE @TotalDays >= @DaysCount           
  BEGIN 
    SET @DayName = DATENAME(dw, (@Todate - @DaysCount))
    
    IF (NOT @DayName = 'Saturday') AND (NOT @DayName = 'Sunday')
    BEGIN
     INSERT INTO @DateList
     SELECT (@ToDate - @DaysCount) AS DAT
    END
    SET @DaysCount = @DaysCount + 1
  
  END
  RETURN
END  
     
 

Send to Friend  Send to friend

Comments

Comment this article
Name:
E-mail:
Comment:
Add Cancel