Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Intermediate
Sep 22, 2016

Skip Weekends SQL Function

Certain reports need to show data that excludes data from weekends. There is a couple of ways to approach this. You could create a Calendar table, but sometimes you do not need to go that far when a simple query would suffice. The following function makes use of the DateAdd SQL function to add 1 to the current date if it is a Sunday, or 2 when it is a Saturday:

CREATE FUNCTION NoWeekends(@AddToDate AS DATE, @NumberOfDays AS INT)
RETURNS DATETIME
AS
BEGIN
    SET @AddToDate = DATEADD(d, @NumberOfDays, @AddToDate)
    IF DATENAME(DW, @AddToDate) = 'sunday'   SET @AddToDate = DATEADD(d, 1, @AddToDate)
    IF DATENAME(DW, @AddToDate) = 'saturday' SET @AddToDate = DATEADD(d, 2, @AddToDate)
  
    RETURN CAST(@AddToDate AS DATETIME)
END
GO

SELECT dbo.NoWeekends(GETDATE(), 3) 
Hannes du Preez
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date