Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

returning data for a given month Expand / Collapse
Author
Message
Posted Monday, January 14, 2008 5:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 11:49 PM
Points: 287, Visits: 407
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.
Post #442406
Posted Monday, January 14, 2008 7:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 10:42 AM
Points: 6,731, Visits: 8,480
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

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


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #442442
Posted Monday, January 14, 2008 1:57 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 9, 2014 2:39 PM
Points: 1,563, Visits: 2,395
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.
Post #442680
Posted Monday, January 14, 2008 2:07 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, October 3, 2014 2:23 AM
Points: 3,108, Visits: 11,503
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'


Post #442685
Posted Monday, January 14, 2008 2:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #442688
Posted Monday, January 14, 2008 4:49 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:14 PM
Points: 35,361, Visits: 31,898
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #442765
Posted Tuesday, January 15, 2008 2:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 11:49 PM
Points: 287, Visits: 407
Thanks everyone for the examples. I appreciate your time
Post #442851
Posted Tuesday, January 15, 2008 2:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:08 AM
Points: 2,365, Visits: 1,846
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"
Post #442864
Posted Tuesday, January 15, 2008 2:50 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 11:49 PM
Points: 287, Visits: 407
many thanks
Post #442867
Posted Tuesday, January 15, 2008 5:03 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:14 PM
Points: 35,361, Visits: 31,898
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #442907
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse