SQL Clone
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
SSC Eights!
SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)

Group: General Forum Members
Points: 972 Visits: 452
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
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28703 Visits: 8986
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


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont 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
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4217 Visits: 2494
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
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13959 Visits: 11848
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
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55701 Visits: 9730
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 Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207704 Visits: 41965
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
mick burden
mick burden
SSC Eights!
SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)

Group: General Forum Members
Points: 972 Visits: 452
Thanks everyone for the examples. I appreciate your time
ChiragNS
ChiragNS
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6477 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
SSC Eights!
SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)

Group: General Forum Members
Points: 972 Visits: 452
many thanks
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207704 Visits: 41965
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

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