September 20, 2011 at 3:13 am
hi,
How to get the month names when i pass startdate and enddate . I need all the month names from startdate to end date .
Ex:(20-09-2011 ,21-12-2011)--> september,october,november and december
September 20, 2011 at 3:41 am
Homework question?
How's this?
DECLARE @startdate DATE = '2011-09-20', @endate DATE = '2011-12-21'
SELECT a.months
FROM (VALUES(1,'January'),(2,'February'),(3,'March'),(4,'April'),(5,'May'),(6,'June'),(7,'July'),
(8,'August'),(9,'September'),(10,'October'),(11,'November'),(12,'December')) a(number,months)
WHERE a.number >= DATEPART(month,@startdate) AND a.number <= DATEPART(month,@endate)
September 20, 2011 at 3:52 am
Thanks fr reply
declare @date1 datetime
declare @date2 datetime
set @date1=GETDATE()
set @date2='2011-12-25'
;with cte as (
select datename(month,@date1) as [Month_Name],@date1 as dat
union all
select datename(month,DateAdd(Month,1,dat)),DateAdd(Month,1,dat) from cte
where DateAdd(Month,1,dat) < @date2)
select [Month_Name] from CTE
this gives correct format if months difference is 20 also it gives correct
September 20, 2011 at 4:06 am
nhimabindhu (9/20/2011)
Thanks fr replydeclare @date1 datetime
declare @date2 datetime
set @date1=GETDATE()
set @date2='2011-12-25'
;with cte as (
select datename(month,@date1) as [Month_Name],@date1 as dat
union all
select datename(month,DateAdd(Month,1,dat)),DateAdd(Month,1,dat) from cte
where DateAdd(Month,1,dat) < @date2)
select [Month_Name] from CTE
this gives correct format if months difference is 20 also it gives correct
Doing it with a recursive CTE in that way will cause you problems. If you need to be able to do it for a big date range, look into a tally table.
e.g.
Try your CTE with the start date of 2000-12-20 and the end of GETDATE: -
DECLARE @date1 DATETIME
DECLARE @date2 DATETIME
SET @date1 = '2000-12-20'
SET @date2 = GETDATE();
WITH cte
AS (
SELECT datename(month, @date1) AS [Month_Name], @date1 AS dat
UNION ALL
SELECT datename(month, DateAdd(Month, 1, dat)), DateAdd(Month, 1, dat)
FROM cte
WHERE DateAdd(Month, 1, dat) < @date2
)
SELECT [Month_Name]
FROM CTE
Recursion error on the CTE.
Now, try that again with a tally table: -
DECLARE @startdate DATE = '2000-12-20', @endate DATE = GETDATE()
--Tally table on the fly, this should be an actual table instead for performance
;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
t4 AS (SELECT 1 N FROM t3 x, t3 y),
tally AS (SELECT DATEADD(MONTH,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,0) AS betweendata
FROM t4 x, t4 y)
--Actual query
SELECT DATENAME(MONTH,betweendata) AS months
FROM tally
WHERE betweendata >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @startdate), 0)
AND betweendata <= DATEADD(MONTH, DATEDIFF(MONTH, 0, @endate), 0)
And we get the 130 months returned.
September 20, 2011 at 4:20 am
DECLARE @SDATE DATETIME
DECLARE @EDATE DATETIME
DECLARE @MLIST TABLE
(MName VARCHAR(30))
SET @SDATE = '2000-09-20'
SET @EDATE = GETDATE()
WHILE (@SDATE < @EDATE)
BEGIN
INSERT INTO @MLIST
SELECT DATENAME(month,@SDATE)
SET @SDATE = DATEADD(MONTH,1,@SDATE)
END
SELECT * FROM @MLIST
September 20, 2011 at 4:32 am
nhimabindhu (9/20/2011)
DECLARE @SDATE DATETIMEDECLARE @EDATE DATETIME
DECLARE @MLIST TABLE
(MName VARCHAR(30))
SET @SDATE = '2000-09-20'
SET @EDATE = GETDATE()
WHILE (@SDATE < @EDATE)
BEGIN
INSERT INTO @MLIST
SELECT DATENAME(month,@SDATE)
SET @SDATE = DATEADD(MONTH,1,@SDATE)
END
SELECT * FROM @MLIST
Really? 😛
WHILE loops are bad in SQL Server, pretty much always. There are exceptions, this isn't one.
Lets use some really unrealistic dates to illustrate, so start date is 1901-01-01 and end date is 4500-01-01
DECLARE @EDATE DATETIME, @SDATE DATETIME
DECLARE @MLIST TABLE
(MName VARCHAR(30))
SET @SDATE = '1901-01-01'
SET @EDATE = '4500-01-01'
WHILE (@SDATE <= @EDATE)
BEGIN
INSERT INTO @MLIST
SELECT DATENAME(month,@SDATE)
SET @SDATE = DATEADD(MONTH,1,@SDATE)
END
SELECT * FROM @MLIST
Just over 3 seconds on my box for your WHILE loop.
How about the tally table?
DECLARE @startdate DATE = '1901-01-01', @endate DATE = '4500-01-01'
--Tally table on the fly, this should be an actual table instead for performance
;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
t4 AS (SELECT 1 N FROM t3 x, t3 y),
tally AS (SELECT DATEADD(MONTH,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,0) AS betweendata
FROM t4 x, t4 y)
--Actual query
SELECT DATENAME(MONTH,betweendata) AS months
FROM tally
WHERE betweendata >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @startdate), 0)
AND betweendata <= DATEADD(MONTH, DATEDIFF(MONTH, 0, @endate), 0)
0.3 seconds, so 10x faster.
Lets try again with even more unrealistic dates, start date 1901-01-01 and end date of 7000-01-01.
Tally table takes 0.5 seconds.
WHILE loop takes 6 seconds.
September 20, 2011 at 4:39 am
option (maxrecursion 0)
now Recursssion error wont occur if i write this code in that program
September 20, 2011 at 4:45 am
nhimabindhu (9/20/2011)
option (maxrecursion 0)now Recursssion error wont occur if i write this code in that program
Yes, but it's still over 2x slower than the tally table solution I suggested.
StartDate '1901-01-01', EndDate '4500-01-01'
Tally Table 0.3 seconds
CTE (with OPTION (maxrecursion 0)) 0.7 seconds
WHILE loop 3 seconds
StartDate '1901-01-01', EndDate '7000-01-01'
Tally Table 0.5 seconds
CTE (with OPTION (maxrecursion 0)) 1.5 seconds
WHILE loop 6 seconds
And bear in mind that these times are including generating the tally table. If I base the tally table query from a properly indexed already created tally table then the query gets even faster.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply