How to Display Date Names between Two Dates in SQL..?


How to Display Date Names between Two Dates in SQL..? In this artical i'm trying to explain display date names between two dates. Here i'm declaring two dates @startdate ,@enddate and then the values are @START_DATE = '2013-06-01',@END_DATE ='2013-06-29'. Now i want to display the names between startdate and enddate..

In this artical i'm trying to explain to display the datenames between two dates.

follow the below steps to achieve this.


Explanation :



1) Find the no.of days between two dates using DateDiff function.

select datediff(day,@start_date,@end_date)


2) Declare the starting count as "0", and end count is no.of days between
two dates

declare @end_count int=(select datediff(day,@start_date,@end_date))
declare @start_count int=0


3) Using While loop you can iterate the function.


while (@start_count < @end_count)
begin
statements
end


Example:






DECLARE @START_DATE DATETIME='2013-06-01'

DECLARE @END_DATE DATETIME ='2013-06-29'

DECLARE @END_COUNT INT=(SELECT DATEDIFF(DAY,@START_DATE,@END_DATE))

DECLARE @START_COUNT INT=0DECLARE @NUMBER INT=1

WHILE (@START_COUNT < @END_COUNT)

BEGIN

DECLARE @RESULT VARCHAR(1000)=(SELECT DATENAME(DW, DATEADD(DAY,@NUMBER,@START_DATE)) as Date_Names)
SET @START_COUNT=@START_COUNT+1
SET @NUMBER=@NUMBER+1
PRINT @RESULT
END




Output:



After execute the above statements the output becomes..

Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday...


Comments

Author: Phagu Mahato13 Oct 2013 Member Level: Gold   Points : 5

Thank Naveen for a wonderful article . We can also apply below code for Display Date Names between Two Dates in SQL
declare @startdate datetime
declare @enddate datetime
declare @days int

select @startdate = '2013/10/13'
select @enddate = '2013/10/23'

select @days = datediff(dd,@startdate,@enddate) +1

select
[Date] = dateaddition(dd,number-1,@startdate),
[Day of Week] = datename(weekday,dateadditionition(dd,number-1,@startdate))
from
dbo.TABLENUMBERRANGE( 1, @days )
order by
number

Other approach for Display Date Names between Two Dates in SQL


Declare @date table(d datetime)
Declare @d datetime

set @day='20120928'

While @day<='20130906'
Begin
Insert into @date values (@d)
set @day=@day+1
End
Select d as DateCol,datename(weekday,dateadd(day,0,d)) as DayName from @date



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: