search date field by partial date?
-
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.
-
Assuming that a parameter called @pSomeDate is a VARCHAR parameter to take the '03/2011', the following should do it.
WITHcteStartOfMonth AS(SELECT StartOfMonth = DATEADD(mm,CAST(LEFT(@pSomeDate,2) AS INT)-1,RIGHT(@pSomeDate,4)))SELECT yada, yada, yadaFROM dbo.YourTable ytCROSS JOIN cteStartOfMonth somWHERE yt.YourDateColumn >= som.StartOfMonthAND yt.YourDateColumn < DATEADD(mm,1,som.StartOfMonth);--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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)
-
I've come up with the following. Thanks for putting me in the right direction
declare @pSomeDate as varchar(20)set @pSomeDate = '04/2011';WITHcteStartOfMonth AS(SELECT StartOfMonth = DATEADD(mm,CAST(LEFT(@pSomeDate,2) AS INT)-1,RIGHT(@pSomeDate,4)))SELECT distinct ChartDateFROM music.dbo.AllTimeTopFortySingles ytCROSS JOIN cteStartOfMonth somWHERE yt.ChartDate >= som.StartOfMonthAND yt.ChartDate < DATEADD(mm,1,som.StartOfMonth)order by ChartDate -
Glad it worked out, Mick. Just to be sure, do you understand how/why it works?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Slightly simpler solution for SQL Server 2008 and later, produces the same results
declare @pSomeDate as varchar(20) = '04/2011';SELECTrecid,weeknumber,title,ChartDateFROM music.dbo.AllTimeTopFortySingles ytWHERE 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}); -
Thanks Jeff, after studying it I've worked out how it works.
-
Thanks for the alt code sscrazy.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply