Truemag

  • Categories
    • Tips And Tricks
    • Internet
    • PHP
    • Javascript
    • CSharp
    • SQL Server
    • Linux
  • Lastest Videos
  • Our Demos
  • About
  • Contact
  • Home
  • Write With Us
  • Job Request
Home SQL Server SQL Server Get list of dates between start date and end date

SQL Server Get list of dates between start date and end date

This tutorial demonstrates 2 ways for you how to obtain the date range in SQL Server based on between date start and end date parameters. One is a algorithm oriented way and the other is a SQL’s function oriented way.

Below is a specified example :
The input :
+ Start date : ‘2012-03-20’
+ End date : ‘2012-03-30’
The output :

Expected list of dates

Expected list of dates

1. The first way : Use simple algorithm loop through from start date until end date as other languagues

CREATE FUNCTION dbo.fGetListOfDates
(
    @startDate DATETIME, -- start date
    @endDate DATETIME    -- end date
)
RETURNS  @retDates TABLE
(    
    [DateAndTime] DATETIME,  -- date time
    NameOfDay VARCHAR(10)    -- name of day
)
AS
BEGIN
    DECLARE @tempDate DATETIME    
    SET @tempDate = @startDate            
    WHILE CONVERT(VARCHAR(10), @tempDate, 120) <=  -- Compare date without time
          CONVERT(VARCHAR(10), @endDate, 120)                
    BEGIN                
        INSERT INTO @retDates
             VALUES (
                        @tempDate,
                        DATENAME(dw, @tempDate)
                    )
        SET @tempDate = DATEADD(D, 1, @tempDate) -- Get next day                 
    END
 
    RETURN    
END

CREATE FUNCTION dbo.fGetListOfDates ( @startDate DATETIME, -- start date @endDate DATETIME -- end date ) RETURNS @retDates TABLE ( [DateAndTime] DATETIME, -- date time NameOfDay VARCHAR(10) -- name of day ) AS BEGIN DECLARE @tempDate DATETIME SET @tempDate = @startDate WHILE CONVERT(VARCHAR(10), @tempDate, 120) <= -- Compare date without time CONVERT(VARCHAR(10), @endDate, 120) BEGIN INSERT INTO @retDates VALUES ( @tempDate, DATENAME(dw, @tempDate) ) SET @tempDate = DATEADD(D, 1, @tempDate) -- Get next day END RETURN END

2. The second way : Use “Recursive Queries Using Common Table Expressions”

CTE – Common Table Expression is one of the new features in SQL SERVER 2005. It can be used instead of temp table or table variable in the stored procedure.

CREATE FUNCTION dbo.fGetListOfDates
(
	@startDate DATETIME, -- start date
	@endDate DATETIME    -- end date
)
RETURNS  @retDates TABLE
(
	[DATE] DATETIME,      -- date time
	NameOfDay VARCHAR(10) -- name of day
)
AS
BEGIN
    WITH tblDates([DATE]) AS
    (
	SELECT @startDate AS [DATE]
	 UNION ALL
	SELECT DATEADD(d,1,[DATE])
	  FROM tblDates
         WHERE [DATE] < @endDate
    )
 
    INSERT INTO @retDates
    SELECT  [DATE], DATENAME(dw,[DATE]) AS NameOfDay
      FROM tblDates 
 
    RETURN
END

CREATE FUNCTION dbo.fGetListOfDates ( @startDate DATETIME, -- start date @endDate DATETIME -- end date ) RETURNS @retDates TABLE ( [Date] DATETIME, -- date time NameOfDay VARCHAR(10) -- name of day ) AS BEGIN WITH tblDates([Date]) AS ( SELECT @startDate as [Date] UNION ALL SELECT DATEADD(d,1,[Date]) FROM tblDates WHERE [Date] < @endDate ) INSERT INTO @retDates SELECT [Date], DATENAME(dw,[Date]) as NameOfDay FROM tblDates RETURN END

3. After create this function,then run this below statement, we will get expected result as the above image

SELECT * FROM dbo.fGetListOfDates('2012-03-20','2012-03-30')

SELECT * FROM dbo.fGetListOfDates('2012-03-20','2012-03-30')

Hope it helpful for you :)!
If you have any way, please let me know by leaving your comment.!

Mar 5, 2012quynhnguyen
C# How to Get key/value pairs from app.config and web.configImplement Google Captcha (reCAPTCHA) With PHP
You Might Also Like:
  • SQL Server Get number of working days and business hours between start date and end date
  • SQL Server get first/last day of previous/current/next month
  • Validate Date With JQuery And Date Object
  • How To Track Website With Multiple Google Analytisc Accounts
  • Facebook Like Button And Recommend Button With fb:like, iframe and html5
  • C# ASP.Net Validate Date
  • Xcode get current date, month, year
  • Capture And Display Execution Time Of SQL Query In SQL Server
  • How To Check And Start An Application If It Is Not Running In Windows
  • C# Get List of databases in SQL Server
quynhnguyen
10 years ago SQL ServerCONVERT, CTE, DATEADD, SQL, Stored Procedure1,762
0
GooglePlus
0
Facebook
0
Twitter
0
Digg
0
Delicious
0
Stumbleupon
0
Linkedin
0
Pinterest
Most Viewed
PHP Download Image Or File From URL
24,542 views
Notepad Plus Plus Compare Plugin
How To Install Compare Text Plugin In Notepad Plus Plus
21,885 views
Microsoft SQL Server 2008 Attach Remove Log
Delete, Shrink, Eliminate Transaction Log .LDF File
17,736 views
JQuery Allow only numeric characters or only alphabet characters in textbox
15,059 views
C# Read Json From URL And Parse/Deserialize Json
11,789 views
4 Rapid Development is a central page that is targeted at newbie and professional programmers, database administrators, system admin, web masters and bloggers.
Recent Posts
  • Things to Learn about Installingderm Loan Type S
  • Online Photo Editor – Free Photoediting Software
  • A Guide to Finding the Best Paper Sellers
  • Photoediting in Home Isn’t Hard to Do!

  • Free Photo Editor Online
Categories
  • CSharp (45)
  • Facebook Graph API (19)
  • Google API (7)
  • Internet (87)
  • iPhone XCode (8)
  • Javascript (35)
  • Linux (27)
  • MySQL (16)
  • PHP (84)
  • Problem Issue Error (29)
  • Resources (32)
  • SQL Server (25)
  • Timeline (5)
  • Tips And Tricks (141)
  • Uncategorized (647)
Recommended
  • Custom Software Development Company
  • Online Useful Tools
  • Premium Themes
  • VPS
2014 © 4 Rapid Development