January 14, 2008 at 5:33 am
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.
January 14, 2008 at 7:37 am
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
Learn to play, play to learn !
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[/url]
- 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
January 14, 2008 at 1:57 pm
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.
January 14, 2008 at 2:07 pm
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'
January 14, 2008 at 2:13 pm
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
January 14, 2008 at 4:49 pm
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
Change is inevitable... Change for the better is not.
January 15, 2008 at 2:00 am
Thanks everyone for the examples. I appreciate your time
January 15, 2008 at 2:46 am
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"
January 15, 2008 at 2:50 am
many thanks
January 15, 2008 at 5:03 am
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
Change is inevitable... Change for the better is not.
January 15, 2008 at 8:26 am
Jeff Moden (1/14/2008)
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...
That's why my example had "dateadd(millisecond, -3, ..." in it. Very important.
- 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
January 15, 2008 at 2:08 pm
The author of the question never answered (as far as I could see) whether or not they wanted february of a specific year or every year's february instead.
The former's been answered but the latter hasn't been answered efficiently. There would be two methods.
1. Have a Dates table where you'd have columns for startOfDay (eg 2008-01-01 00:00.000) and nextDay (2008-01-02 00:00.000). You'd join to your table using these columns with a >= and <. Some may prefer to have endOfDay (2008-01-01 23:59.997) instead of nextDay and join wtih <= instead.
In the date table you'd also have columns for (examples) dayOfWeek, Month, MonthName, Year, DayOfMonth, etc, etc. These columns would be indexed. The you could filter based on the Month or MonthName columns.
2. Add a computed column to the original table called dateCol_month. Then add an index on this column (look in SQL Books Online for the SET options that may need to be specified if you're indexing on computed columns). You can then filter easily on this column.
January 15, 2008 at 2:27 pm
Ian Yates (1/15/2008)
The author of the question never answered (as far as I could see) whether or not they wanted february of a specific year or every year's february instead.The former's been answered but the latter hasn't been answered efficiently. There would be two methods.
1. Have a Dates table where you'd have columns for startOfDay (eg 2008-01-01 00:00.000) and nextDay (2008-01-02 00:00.000). You'd join to your table using these columns with a >= and <. Some may prefer to have endOfDay (2008-01-01 23:59.997) instead of nextDay and join wtih <= instead.
In the date table you'd also have columns for (examples) dayOfWeek, Month, MonthName, Year, DayOfMonth, etc, etc. These columns would be indexed. The you could filter based on the Month or MonthName columns.
2. Add a computed column to the original table called dateCol_month. Then add an index on this column (look in SQL Books Online for the SET options that may need to be specified if you're indexing on computed columns). You can then filter easily on this column.
It is very unusual for a report to want only a month, no matter the year. However, if that is what is needed, it is likely that either of those methods will just result in a table scan; SQL Server tends ignore an indexes with low cardinality, like an index on month.
This would probably be just as good, but it is always good to test alternatives.
where month(MyDate) = 2
January 15, 2008 at 4:18 pm
GSquared (1/15/2008)
Jeff Moden (1/14/2008)
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...
That's why my example had "dateadd(millisecond, -3, ..." in it. Very important.
Understood and good job... but (you'll probably think I'm pretty far off the wall and I might be) but if MS ever decides to go to microseconds like some RDBMS's have, subtracting 3 millisconds will still cause unexpected results. The < wholedate method would still survive, though.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 15, 2008 at 5:20 pm
Jeff Moden (1/15/2008)
GSquared (1/15/2008)
Jeff Moden (1/14/2008)
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...
That's why my example had "dateadd(millisecond, -3, ..." in it. Very important.
Understood and good job... but (you'll probably think I'm pretty far off the wall and I might be) but if MS ever decides to go to microseconds like some RDBMS's have, subtracting 3 millisconds will still cause unexpected results. The < wholedate method would still survive, though.
You mean, when DateTime2 is in effect in a few months (in 2008?). Bound to be fun....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply