Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


returning data for a given month


returning data for a given month

Author
Message
mick burden
mick burden
Old Hand
Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)

Group: General Forum Members
Points: 305 Visits: 431
What would be the easiest way of returning data from a table for a given month? I know I could write a SP something like

select * from table where [dtfrom] = '28/02/2008'

What I'd like is
select * from table where [dtfrom] = 'february'
bearing in mind I may or may not know what date is the last date of that month.
ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6984 Visits: 8839
you could use :

In your proc just set the dateranges (could use an available index (sargeble))

select ...
from yourobject
where mydatetimecol between @dateBegin
and @dateEnd


or
use the less performant (because non-sargeble)

select ...
from yourobject
where datepart(mm,mydatetimecol ) = @monthNo



Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution" :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Greg Snidow
Greg Snidow
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1611 Visits: 2478
You could also try something like...
SELECT YourDateCol
WHERE DATENAME(m,YourDateCol) = 'February', or what ever month you want.

Greg

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3264 Visits: 11771
Greg Snidow (1/14/2008)
You could also try something like...
SELECT YourDateCol
WHERE DATENAME(m,YourDateCol) = 'February', or what ever month you want.

Greg


That is not a good way to do a date range query.
1. It does not specify the year.
2. It is language setting dependent.
3. It prevents it from using any index on the date column.

The following is usually the best way to do a date range query:
where Mydate >= StartDateTime and  Mydate < EndDateTime

-- To get data from January of 2007
where Mydate >= '20070101' and Mydate < '20070201'


GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14393 Visits: 9729
Keep in mind that the one with "between" will be much easier on the server than the ones that have functions in the Where clause.

Also, unless you've already handled this (can't tell from the question), do remember to have the year in your Where clause while you're at it. Otherwise, you'll get data from every February, regardless of year. (If that's what you want, fine, but most of the time you want just one specific February.)

The "between" version (first reply) will also handle the year. And can easily be used for things like quarterly reports (just use a wider set of dates in the parameters).

And, if all you have is the month (and year), you can use this to get the dates for the first and last:

declare @From datetime, @To datetime
select @From = cast(@Month + ' 1, ' + @Year as datetime),
@To = dateadd(millisecond, -3, dateadd(month, 1, cast(@Month + ' 1, ' + @Year as datetime)))

select col1
from dbo.Table
where date between @From and @To

(Of course, you'll have to put in your own date and year variables. I assume they are both strings [varchar] in this example, otherwise they will have to be converted in the initial select statement.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45474 Visits: 39948
Heh... Keep in mind that the BETWEEN examples are rarely correct because they don't allow all times of the day to be included or they mistakenly include dates with midnight times as part of the "end date".

Michael did it right...

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
mick burden
mick burden
Old Hand
Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)

Group: General Forum Members
Points: 305 Visits: 431
Thanks everyone for the examples. I appreciate your time
ChiragNS
ChiragNS
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2409 Visits: 1865
between '2008/01/01 12:00:00:000 AM' and '2008/01/01 11:59:59:997 PM' if u want all records between the 2 dates.

"Keep Trying"
mick burden
mick burden
Old Hand
Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)

Group: General Forum Members
Points: 305 Visits: 431
many thanks
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45474 Visits: 39948
Chirag (1/15/2008)
between '2008/01/01 12:00:00:000 AM' and '2008/01/01 11:59:59:997 PM' if u want all records between the 2 dates.


Just a suggestion...

The optimizer is going to evaluate the BETWEEN code in the quote above as ...
  WHERE SomeDate >= '2008/01/01 12:00:00:000 AM' 
AND SomeDate <= '2008/01/01 11:59:59:997 PM'



... anyway... that will be almost the same as what the code below which the optimizer also resolves as...

WHERE somedatecolumn >= '2008/01/01' 
AND somedatecolumn < '2008/01/02'



The point is, there's no real performance or index usage difference so far as what the optimizer will do. But, there is the PITA factor if you want to return a whole month. For example, to return the whole month of January 2008 using the BETWEEN code, you must first know what the end date for the month is, add '23:59:59:997' to it and then use the BETWEEN.

To do the same thing using the AND method... you just add a month... and don't worry about what the end date for the month is...

WHERE somedatecolumn >= '2008/01/01' 
AND somedatecolumn < '2008/02/01'



If you get into the habit of the AND method, you'll never forget to add the time, you'll never need to calulate the end date for a month, and the format holds true no matter which whole date/time element you're searching by. For example, you don't have to use '59:59:997' if you're seaching for a whole hour... just search for something LESS than the NEXT hour.

From a readability standpoint (at least to me), it's a lot easier on the eyes to look at whole dates that juggling '23:59:59:997' or just '59:59:997' everywhere in the code.

Anyway, like I said... just a suggestion...

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search