Click here to Skip to main content
13,000,469 members (59,358 online)
Click here to Skip to main content
Add your own
alternative version

Stats

28.8K views
19 bookmarked
Posted 20 Jul 2008

Select dates of a day between two dates.

, 20 Jul 2008
Rate this:
Please Sign up or sign in to vote.
This function gives dates for a day between two dates (e.g. All sundays from Jan-08 to Aug-08)

Introduction

To list out dates of particular day between two days is needed when we are developing any application which having weekly status functinalities. So listing dates of partiuclar day is very help ful in such situtation. fnGetDatesforAday Function gives dates for a day between two dates.

Background

Once I need to list all Sundays betweeen two dates so that time I wrote the code for the same. Later I converted that code to generalize for all days the result is this function.

This function recieves three parameter DateFrom, DateTo and DayName.

  • DateFrom : Date from which we want the dates of a day. It accepts date in two formats “MM/DD/YYY” and “MM-DD-YYYY”.
  • DateTo : Date upto which we want the dates of a day. It accepts date in two formats “MM/DD/YYY” and “MM-DD-YYYY”.
  • DayName: Day name for which we need to list dates. Valid values are “Sunday”, “Monday”, ”Tuesday”, ”Wednesday”, “Thursday”, ”Friday” and ”Saturday”.

This function uses two system function

  • DATEDIFF
  • DATENAME

The Logic here is first find out the number of days between two dates using DATEDIFF function which returns number of days. So using this number we can loop through each day and check for the required day in the days between start date to end date.

To get day name we use DATENAME function which gives us the day names like “Sunday”, “Monday”, “Tuesday”, “Wednesday”, “Thursday”, “Friday” and “Saturday”, so compare the given day with these days If found insert the date into the table. So finally the table contains all required days dates in the table. Finally return all the dates which are inserted in the table.

Using the code

You can use this code in the select statement or in the stored procedure where you need a date list for the particular day.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Shivshanker cheral
-- Create date: <date month="7" day="18" year="2008" />18 July 2008
-- Description:   To Get any weekday dates between two dates 
-- =============================================

CREATE FUNCTION fnGetDatesforAday
(
      -- Add the parameters for the function here
      @DtFrom DATETIME,
      @DtTo DATETIME,
      @DayName VARCHAR(12)
)

RETURNS @DateList TABLE ([Day] varchar(20),Dt datetime)

AS
BEGIN
      IF NOT (@DayName = 'Monday' OR @DayName = 'Sunday' OR @DayName = 'Tuesday' OR @DayName = 'Wednesday' OR @DayName = 'Thursday' OR @DayName = 'Friday' OR @DayName = 'Saturday')
      BEGIN
            --Error Insert the error message and return

            INSERT INTO @DateList
            SELECT 'Invalid Day',NULL AS DAT
            RETURN
      END 

      DECLARE @TotDays INT
      DECLARE @CNT INT

      SET @TotDays =  DATEDIFF(DD,@DTFROM,@DTTO)-- [NO OF DAYS between two dates]

      SET @CNT = 0
      WHILE @TotDays >= @CNT        -- repeat for all days 
      BEGIN
        -- Pick each single day and check for the day needed
            IF DATENAME(DW, (@DTTO - @CNT)) = @DAYNAME
            BEGIN
                  INSERT INTO @DateList
                 SELECT @DAYNAME,(@DTTO - @CNT) AS DAT
            END
            SET @CNT = @CNT + 1
      END
      RETURN
END

GO 
      

Output:
 SELECT [Day],[Dt] FROM dbo.fnGetDatesforAday('7/1/2008','8/31/2008','Sunday')
 Day Dt
 -------------------- -----------------------
 Sunday 2008-08-31 00:00:00.000
 Sunday 2008-08-24 00:00:00.000
 Sunday 2008-08-17 00:00:00.000
 Sunday 2008-08-10 00:00:00.000
 Sunday 2008-08-03 00:00:00.000
 Sunday 2008-07-27 00:00:00.000
 Sunday 2008-07-20 00:00:00.000
 Sunday 2008-07-13 00:00:00.000
 Sunday 2008-07-06 00:00:00.000 
(9 row(s) affected)



We can list the dates in sorted order with date in different formats according to our needs using convert function and Order by clause.

SELECT [Day],CONVERT(VARCHAR,[Dt],105) AS [Date] FROM dbo.fnGetDatesforAday('7/1/2008','8/31/2008','Sunday') ORDER BY [Dt]

Day                  Date
-------------------- ------------------------------
Sunday               06-07-2008
Sunday               13-07-2008
Sunday               20-07-2008
Sunday               27-07-2008
Sunday               03-08-2008
Sunday               10-08-2008
Sunday               17-08-2008
Sunday               24-08-2008
Sunday               31-08-2008

(9 row(s) affected)


For error it gives the result as Invalid Day in the Day column and Null in Date column

SELECT [Day],CONVERT(VARCHAR,[Dt],105) AS [Date] FROM dbo.fnGetDatesforAday('7/1/2008','8/31/2008','Sumday') ORDER BY [Dt]

Day                  Date
-------------------- ------------------------------
Invalid Day          NULL

(1 row(s) affected)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

s.s.cheral
Technical Lead Software Paradigms Infotech Pvt Ltd. Mysore, Karna
India India
Currently working as Technical Lead. I have total of 15+ Years of experience in Computer science.

I have 9 Years and 9 months of experience in Software Development with Microsoft Technologies.
C#, VB.NET, ASP.NET, MS-SQL server, VBA (Excel macros), WPF with MVVM light, LINQ, WCF. Exposure to MVC, Entity framework, SSRS, SSIS.
Well versed in OOPS concepts and has good knowledge of design patterns and design principles.

Education: M. Sc (CS) Regular, M. Tech (S/W Eng.) (Correspondence), M. Phil. in CS (Correspondence).
Microsoft Certification: Exam 480 Programming in HTML5 with JavaScript and CSS3 Completed Successfully on Feb 18, 2013.

Worked as Teaching Faculty for 5 Years
• Teaching the computer science subjects to the M. Sc., BCA, and PGDCA students. The subjects are Analysis and design of algorithm, Computer organization architecture, Data Structure, Computer Networks, Database management system, Expert systems, Computer Graphics, Compiler design and System Programming.
Academic Project Guide
• Guided M. Sc. and PGDCA students for their academic projects work, affiliation Gulbarga University Gulbarga.

You may also be interested in...

Comments and Discussions

 
QuestionToo complicated and region-dependent ! Pin
Georg Scholz11-Jun-15 0:33
memberGeorg Scholz11-Jun-15 0:33 
SuggestionMore clean & simple demonstration Pin
KrishanGahlot17-Sep-12 21:42
memberKrishanGahlot17-Sep-12 21:42 
GeneralAuxiliary Table... Pin
rippo21-Jul-08 10:11
memberrippo21-Jul-08 10:11 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170624.1 | Last Updated 21 Jul 2008
Article Copyright 2008 by s.s.cheral
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid