January 10, 2015 at 4:20 pm
on one of my tables I've a field set as smalldatetime, which is populated by various dates ranging between 01/01/2000 and 01/01/2015. What I'm after is being able to pass in a partial date such as 03/2011 and get a record set of all the dates in the table that falls in March 2011, prefably distinct. Can anyone help please.
January 10, 2015 at 9:18 pm
Assuming that a parameter called @pSomeDate is a VARCHAR parameter to take the '03/2011', the following should do it.
WITH
cteStartOfMonth AS
(
SELECT StartOfMonth = DATEADD(mm,CAST(LEFT(@pSomeDate,2) AS INT)-1,RIGHT(@pSomeDate,4))
)
SELECT yada, yada, yada
FROM dbo.YourTable yt
CROSS JOIN cteStartOfMonth som
WHERE yt.YourDateColumn >= som.StartOfMonth
AND yt.YourDateColumn < DATEADD(mm,1,som.StartOfMonth)
;
--Jeff Moden
Change is inevitable... Change for the better is not.
January 11, 2015 at 2:45 am
That's great Jeff, based on your example I ended up with, which works well. Is there a way I can return distinct values in ChartDate ?
declare @pSomeDate as varchar(20)
set @pSomeDate = '04/2011';
WITH
cteStartOfMonth AS
(
SELECT StartOfMonth = DATEADD(mm,CAST(LEFT(@pSomeDate,2) AS INT)-1,RIGHT(@pSomeDate,4))
)
SELECT recid, weeknumber, title,ChartDate
FROM music.dbo.AllTimeTopFortySingles yt
CROSS JOIN cteStartOfMonth som
WHERE yt.ChartDate >= som.StartOfMonth
AND yt.ChartDate < DATEADD(mm,1,som.StartOfMonth)
January 11, 2015 at 5:52 am
I've come up with the following. Thanks for putting me in the right direction
declare @pSomeDate as varchar(20)
set @pSomeDate = '04/2011';
WITH
cteStartOfMonth AS
(
SELECT StartOfMonth = DATEADD(mm,CAST(LEFT(@pSomeDate,2) AS INT)-1,RIGHT(@pSomeDate,4))
)
SELECT distinct ChartDate
FROM music.dbo.AllTimeTopFortySingles yt
CROSS JOIN cteStartOfMonth som
WHERE yt.ChartDate >= som.StartOfMonth
AND yt.ChartDate < DATEADD(mm,1,som.StartOfMonth)
order by ChartDate
January 11, 2015 at 10:07 am
Glad it worked out, Mick. Just to be sure, do you understand how/why it works?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 12, 2015 at 12:11 am
Slightly simpler solution for SQL Server 2008 and later, produces the same results
😎
declare @pSomeDate as varchar(20) = '04/2011';
SELECT
recid
,weeknumber
,title
,ChartDate
FROM music.dbo.AllTimeTopFortySingles yt
WHERE yt.ChartDate >= CONVERT(SMALLDATETIME,'01/' + @pSomeDate,103)
AND yt.ChartDate < DATEADD(MM,1,CONVERT(SMALLDATETIME,'01/' + @pSomeDate,103));
Quick thought, adding an index on the date column including the output column would be very beneficial.
CREATE NONCLUSTERED INDEX [Index Name] ON [TABLE_SCHEMA].
([DATE COLUMN] ASC) INCLUDE ([OUTPUT COLUMNS});
January 12, 2015 at 2:33 am
Thanks Jeff, after studying it I've worked out how it works.
January 12, 2015 at 2:34 am
Thanks for the alt code sscrazy.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply