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...
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