Select Records For Any Month

Question

Post reply

Select Records For Any Month

Ashwin9

SSC-Addicted

Points: 477
More actions
April 26, 2011 at 2:05 am

#143810

Hi All,
I've dates stored in my Table
MyDate
------------------------------------
2011-03-03 12:17:48.000
2011-04-04 12:17:48.000
2011-04-12 12:17:48.000
2011-04-16 12:17:48.000
2011-04-16 12:17:48.000
....
....
....
------------------------------------
I want to select the records as per months requirement.
Means than if I want to select 3rd month record then I should give records related to 3rd months.
Same way for 4th month.
Also, If I compare only "3" Instead of "03" or "4" for "04" month the records should be displayed.
Can anybody suggest me the query for this?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

A few items here.

One, this field is a DATETIME, right? There's no DDL so I'm assuming it is.

Second, what parameters are you passing in? Just passing in a '4' or '04' is going to give you April for every year in the table, unless you're specifying the year as well.

Assuming you're passing in the month and year, what I'd recommend doing so you can use indexes well is this:

DECLARE @year INT, @month INT, --These are your parameters

@startmonth DATETIME --local proc variable

-- Some parameter settings:

SELECT @year = 2011, @month = 4

--Set @startmonth = 04/01/2011

SET @startmonth = DATEADD( yy, @year - 1900, DATEADD( mm, @month, 0))

SELECT * FROM table WHERE dtField >= @startmonth AND dtfield < DATEADD(ms, -2, DATEADD( mm, 1, @startmonth)) -- Add a month and remove two milliseconds due to rounding

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

--First, lets build some test data

DECLARE @table AS TABLE (mydate DATETIME)

;WITH testdata AS (

SELECT CAST('2011-01-01' AS DATETIME) datevalue, 1 AS counting

UNION ALL

SELECT datevalue + Round(( ( 5 ) * Rand() + 1 ), 0), counting + 1

FROM testdata

WHERE counting + 1 < 101)

--100 rows of test data

INSERT INTO @table (mydate)

SELECT datevalue

FROM testdata

--Query

DECLARE @year INT, @month INT

SET @year = 2011

SET @month = 04

SELECT mydate

FROM @table

--Change the "3" to whichever month you require

WHERE MONTH(mydate) = @month AND YEAR(mydate) = @year

--EDIT--

Ack, ninja'd. I've made the same assumptions as Craig in my query. His is more optimised, since it'll use any indexes you have on the "mydate" column whereas mine won't. I wasted too much time making up some test-data since you didn't supply any.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Vedran Kesegic SSCrazy Eights Points: 9302 More actions · Answer 3

I've dates stored in my Table
MyDate
------------------------------------
2011-03-03 12:17:48.000
2011-04-04 12:17:48.000
2011-04-12 12:17:48.000
2011-04-16 12:17:48.000
2011-04-16 12:17:48.000
....
------------------------------------
I want to select the records as per months requirement.
Means than if I want to select 3rd month record then I should give records related to 3rd months.
Same way for 4th month.
Also, If I compare only "3" Instead of "03" or "4" for "04" month the records should be displayed.

First, is this a DATETIME column? If it isn't - it should be: convert it.

Second, if you want your query to be really fast, you NEVER put any function around your date column (in a WHERE condition). Not even a "MONTH(yourdatefiled)" or even "yourdatefiled+1" or even "yourdatefiled+0". Your date field should be "bare" and solo on it's side of condition if you want sql server to be able to use the index. (You have the index on a date column, right?)

Third, never substract a second, milisecond, nanosecond, 2 ms or anything for such purposes. Substracting anything is innacurate and a very nasty BUG! For example, later you decide to change to DATETIME2 datatype which allows for greater precision than 2ms and this code will no longer work correctly. It will skip the records falling in this 2ms you just substracted. Big no-no and a very common mistake. Completely accurate is to use "<" (strictly lower than) operator on upper, nice and round limit.

That combined will give you the fastest and deadly-accurate solution:

-- Create test data

--DROP TABLE #tDates

CREATE TABLE #tDates ( SomeTime DATETIME )

CREATE INDEX #tDates_IX_d ON #tDates(SomeTime)

INSERT INTO #tDates(SomeTime) VALUES('2011-03-03 12:17:48.000')

INSERT INTO #tDates(SomeTime) VALUES('2011-04-04 12:17:48.000')

INSERT INTO #tDates(SomeTime) VALUES('2011-04-12 12:17:48.000')

INSERT INTO #tDates(SomeTime) VALUES('2011-04-16 12:17:48.000')

-- Define input variable

DECLARE @MonthInput VARCHAR(2) SET @MonthInput = '4'

-- Find first day of given month in year of 2011

DECLARE @FirstDayOfMonth DATETIME

SET @FirstDayOfMonth = CONVERT(DATETIME, '2011-'+@MonthInput+'-1', 120)

-- Let's kick it

SELECT d.SomeTime

FROM #tDates d

WHERE d.SomeTime >= @FirstDayOfMonth AND d.SomeTime < DATEADD(month,1,@FirstDayOfMonth)

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

@Vedran,

Great post and absolutely correct on all accounts. It's not often that I see people with only 68 points turning out such a nice, informative answer like you did. I hope you don't mind an ol' salt throwing in a couple of tips.

When you make claims like...

That combined will give you the fastest ...

... you're just perpetuating "internet myth" when you only have 5 rows of data. Take it to the next level and [font="Arial Black"]really [/font]show people what you mean. It doesn't take long, either...

--=================================================================================================

-- Create a test table and populate it with data.

--=================================================================================================

--===== Conditionally drop the test table just to make reruns easier in SSMS

IF OBJECT_ID('tempdb..#SomeTestTable','U') IS NOT NULL

DROP TABLE #SomeTestTable

;

GO

--===== Declare some obviously named variables to make testing easier

DECLARE @StartDate DATETIME, --Inclusive

@EndDate DATETIME --Non Inclusive

;

--===== Assign a range of dates (not checked for proper order)

SELECT @StartDate = '2010', --Yeah... whole years work just fine.

@EndDate = '2020' --Don't forget... Non Inclusive

;

--===== Generate enough data to support your claim.

-- I usually use a million rows which not too many people can argue with. ;-)

-- If they can (with code), then I learn something in the process, as well.

-- This one takes less than 4 seconds to run.

SELECT TOP 1000000

SomeDateTime = RAND(CHECKSUM(NEWID())) * DATEDIFF(dd,@StartDate,@EndDate) + @StartDate

INTO #SomeTestTable

FROM sys.all_columns ac1 --Even a brand new installation has at least 4,000 rows

CROSS JOIN sys.all_columns ac2 --Cross Join will support at least 16,000,000 rows if you need it

;

--===== Now add the index as you did before.

-- This takes less than 9 seconds (I know... slow. My desktop is 9 years old)

CREATE INDEX IX_#SomeTestTable_SomeDateTime ON #SomeTestTable(SomeDateTime)

;

--===== This just proves the Domain of the dates and doesn't need to be included

-- unless someone doesn't believe you or unless you just want to show the proof.

SELECT MIN(SomeDateTime),MAX(SomeDateTime)

FROM #SomeTestTable

;

First, is this a DATETIME column? If it isn't - it should be: convert it.

Remember that the OP started the post because (s)he doesn't know about dates and probably has no clue as to why you're saying it should be converted. Again, my suggestion is to help prevent "internet myth" and turn it into an extra learning opportunity above and beyond what you did in an otherwise outstanding post. Tell people the reasons WHY (performance, ease of manipulation, sortable, no conversions required for other date math, etc.) they should convert it. 🙂

Like I said, I hope you don't mind the suggestions. It's not my job to judge but I like what I saw in your reply above and want to help you take it over the top. 🙂

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

skcadavre (4/26/2011)
First, lets build some test data...
...
I wasted too much time making up some test-data since you didn't supply any.

Nah... you did good. It's never a waste of time to make test data. Take a look at my post above. You can gen a pot wad of data in a heartbeat and a half. Keep in mind that most of my "code" above is comments. Unless you're a "documentin' fool" like me, you can boil it down to something very simple, like this.

SELECT TOP 1000000

SomeDateTime = RAND(CHECKSUM(NEWID())) * DATEDIFF(dd,'2010','2020') + CAST('2010' AS DATETIME)

INTO #SomeTestTable

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2;