If you look at optimisation you wil find that doing the sub selects is faster as they only get performed once. using functions on the outer select can be more intensive.
So I would suggest Gavin's way is ok. The only thing that might help is to make the date range outside the select as a local variable. Simon 2009/2/23 Kevan Stannard <kevanstann...@gmail.com> > > Perhaps you only need to consider the year and month? > > select count(*) as monthOpenTotal > from tbl_car > where > year(dateRaised) = year(getdate()) > and month(dateRaised) = month(getdate()) > > > > 2009/2/23 Gavin Baumanis <beauecli...@gmail.com>: > > > > Hi Everyone, > > > > Just a quick question to ask how you all create your date ranges? > > For example I have a requirement to list all records where a datefield > > is between the first and las day of THIS month. > > > > I use the following, but just wanted some verification, I suppose, > > that there wasn't a better way to do it. > > (Ms-Sql Server 2005) > > > > SET DATEFORMAT ymd > > DECLARE @today DATETIME > > > > SET @today = CONVERT(CHAR(8), GETDATE(), 112) > > > > Select > > count(*) as monthOpenTotal > > from > > tbl_car > > where > > dateRaised between > > (SELECT > DATEADD(DAY, -DATEPART(DAY, @today) + 1, @today)) > > and (SELECT DATEADD(DAY, > -DATEPART(DAY, @today), DATEADD(MONTH, > > 1, @today)+ ' 23:59:59')) > > > > > > Thanks for any ideas you might have. > > > > > > > > > > > -- Cheers Simon Haddon Woman loves feeling danger and speed. That is why woman wants man. They get a speed rush that is the most dangerous of all. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~----------~----~----~----~------~----~------~--~---
